'Database' Category

  • Install a new version of PostgreSQL server

    January 5, 2018
    Install a new version of PostgreSQL server. dpkg-query -l postgresql* Looks like the Debian upgrade included PostgreSQL 9.6, but I still need to upgrade from 9.1 to 9.6. Run `pg_lsclusters`, your 9.1 and 9.6 main clusters should be “online”. pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.1 main 5432 online postgres /var/lib/postgresql/9.1/main […]

    posted in Postgres by nico

  • Postgres equivalent show create table

    February 27, 2016
    Problem solved ! Here is a pure sql solution, how to get all sql from all tables in Postgres: — — — NB 27.02.16 — List sql tables — show_create_table() — — SELECT ‘CREATE TABLE ‘||sql.table||'(‘ ||array_to_string(array_agg(sql),’, ‘) ||’)’ as sql FROM ( ( SELECT — FIELDS c.oid AS id ,c.relname as table ,9 as […]

    posted in Database, Postgres by nico

  • Sphinx Search

    April 13, 2014
    I use Sphinx to search text in 5 millions records. Mysql is not as good, sphinx is a deticated read only database to search text. The performance are so good, that I use sphinx for everything, even for regular queries. I managed to reduce the load of my servers by a factor of 10 ! […]

    posted in Database, Developer, System Administrator by nico

  • Mysql

    April 13, 2014
    MySQL is a database wich support master/slave replication system. Lock the database to run a command: FLUSH TABLES WITH READ LOCK; SYSTEM sh -c ‘backup.sh’; UNLOCK TABLES; Pros: The master/slave replication is mature and powerfull Innodb provide good performance on big tables Cons: Can’t find any for my use

    posted in Database, Developer, System Administrator by nico

  • WordPress

    April 13, 2014
    WordPress is a great solution to generate quickly a website with articles. Wordpress use php and a mysql database. Here is the database scheme. You can find many plugins for wordpress to suit it to you need. You can easly create some template for the apparence, or you can just download templates made by others.

    posted in Database, Developer, Framework by nico