PostgreSQL

Peupler des tables

  • Peupler une table rapidement avec des données aléatoires :
INSERT INTO Log(level, message)
SELECT 'level2' as level, md5(random()::text) AS descr
	FROM (SELECT * FROM generate_series(1,10*1000*1000) AS id) AS x;

Création de base

  • Create the filesystem

      $ export PGROOT="/var/lib/postgres"
      $ mkdir -p $PGROOT/data && chown postgres.postgres $PGROOT/data
      $ su - postgres -c "/usr/bin/initdb -D $PGROOT/data"
    
  • Make it UTF8 by default

      $ su - postgres -c "/usr/bin/initdb -E utf8 --locale=en_US.UTF-8 $PGROOT/data"
    
  • Create Databases

      $ createdb -O owner -T some_template database_name
    
    • By default, PostgreSQL listens on TCP port 5432.

Export de base

  • Dump all databases

      $ pg_dumpall --clean > databases.sql
    
  • Dump a database with compression (-Fc)

      $ pg_dump -Fc --file=database.sql --clean database
    
  • Dump a table

      pg_dump [-d database] [--schema schema] -t table
    
  • Dump a table definition (no data)

      pg_dump -s [-d database] [--schema schema] -t table
    

Restauration de base

  • Restore a database from a dump file

      pg_dump -Ft [-h host] [-p port] [-U username[  [--schema=schema] -F c -b -v -f 	<path_to_dump_file> <db_name>
    
    • -p, –port=PORT database server port number
    • -i, –ignore-version proceed even when server version mismatches
    • -h, –host=HOSTNAME database server host or socket directory
    • -U, –username=NAME connect as specified database user
    • -W, –password force password prompt (should happen automatically)
    • -d, –dbname=NAME connect to database name
    • -v, –verbose verbose mode
    • -F, –format=c t p output file format (custom, tar, plain text)
    • -c, –clean clean (drop) schema prior to create
    • -b, –blobs include large objects in dump
    • -v, –verbose verbose mode
    • -f, –file=FILENAME output file name
  • Restore a database

      $ pg_restore -Fc database.sql
    
      pg_restore [-h host] [-p port] [-U user] [--schema=schema] -d database -v -c <path_to_dump_file>
    
    • -p, –port=PORT database server port number
    • -i, –ignore-version proceed even when server version mismatches
    • -h, –host=HOSTNAME database server host or socket directory
    • -U, –username=NAME connect as specified database user
    • -W, –password force password prompt (should happen automatically)
    • -d, –dbname=NAME connect to database name
    • -v, –verbose verbose mode
    • -c, –clean Clean (drop) database objects before recreating them
  • Reset password of postgres user

      # su postgres
      # psql -d template1
      template1=# ALTER USER postgres WITH PASSWORD '${POSTGRESQL_POSTGRES_PASSWORD}';
    

psql

  • Psql - show a list of databases
    • => \l
    • Lowercase L, not the number 1
  • Psql - show all users
    • => select * from pg_user;
  • Psql - show all tables (including system tables)
    • => select * from pg_tables;{.sql}
  • Psql - show tables in the current context (database/schema)
    • => \d
  • Psql - change current database
    • => \c database;
  • Psql - show all schemas in the current database
    • => \dn
  • Psql - Grant permissions on a schema to a user
    • => GRANT ALL ON myschema TO user;{.sql}
  • Psql - quit psql
    • => \q
  • Psql - show help
    • => \?
  • Psql - copy a table to a tab delimeted file
    • => COPY table TO 'table.txt';{.sql}
  • Psql - load a table from a tab delimeted file
    • => COPY table FROM 'table.txt';{.sql}
  • Psql - show permissions on database objects
    • => \z [object]
    • r – SELECT{.sql} (“read”)
    • w – UPDATE{.sql} (“write”)
    • a – INSERT{.sql} (“append”)
    • d – DELETE{.sql}
    • R – RULE{.sql}
    • x – REFERENCES{.sql} (foreign keys)
    • t – TRIGGER{.sql}
    • X – EXECUTE{.sql}
    • U – USAGE{.sql}
    • C – CREATE{.sql}
    • T – TEMPORARY{.sql}
    • arwdRxt – ALL PRIVILEGES{.sql} (for tables)
    • – grant option for preceding privilege
    • /yyyy – user who granted this privilege
  • Run the vacuum utility
    • => vacuumdb --verbose --analyze --all{.bash}
    • Note:
      • vacuum reclaims space from deleted records and updates indexes.
      • It should be set up in cron.
      • Newer versions of postgresql may run vacuum automatically.
      • Increase perfomance with shared memory

PostgreSQL Examples

  • Adding new user called BRIAN:

      $ sudo su - postgres
      $ createuser --createdb --username postgres --no-createrole \\
       		--pwprompt BRIAN
          Enter password for new role:
          Enter it again:
          Shall the new role be a superuser? (y/n) n
      $ exit
    
  • ALTER TABLE

    • Add a unique constraint to the email column in the customer table
      ALTER TABLE customer ADD CONSTRAINT customer_email_key UNIQUE (email);
    

PostgreSQL-Snippet

Créer un utilisateur et lui donner les droits sur une DB

$ su - postgres
CREATE USER tom WITH PASSWORD 'myPassword';
CREATE DATABASE jerry;
GRANT ALL PRIVILEGES ON DATABASE jerry to tom;
\q

Changer le propriétaire d’une base

ALTER TABLE climate.measurement OWNER TO postgres;

Licence

logo creative common by-sa 3.0 Creative Commons Paternité – Partage à l’Identique 3.0 non transcrit