PostgreSQL

change to postgres user and open psql prompt

1
# su - postgres
# psql

list databases

1
postgres=# \l

list roles

1
postgres=# \du

create role

1
postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

create role with multiple privileges

1
postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD
postgres=# 'password1' CREATEDB CREATEROLE REPLICATION SUPERUSER;

alter role

1
postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

drop role

1
postgres=# DROP ROLE demorole1;

create database

1
postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

grant privileges to new user

1
postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

drop database

1
postgres=# DROP DATABASE demodb1;

connect to database

1
postgres=# \c <databasename>

list tables in connected database

1
postgres=# \dt

list columns on table

1
postgres=# \d <tablename>

show processlist;

1
SELECT * from pg_stat_activity ;

Kill session on a database

1
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydataabse' AND leader_pid IS NULL;

nice output

1
postgres=# \x on

output in csv file

1
postgres=#
\copy (
select * from truc
) To 'output.csv' With CSV DELIMITER ';' HEADER

kill query

1
postgres=# SELECT pg_cancel_backend(<pid of the process>)
postgres=# SELECT pg_terminate_backend(<pid of the process>)

show tables

1
SELECT * FROM pg_catalog.pg_tables;

backup/restore database

1
$ pg_dump -h ${TARGET_HOST} -p ${PG_PORT} -U ${PG_USER} -Fd -f ${TARGET_DIR} ${PG_DB}
$ pg_restore -c -Fd -d ${PG_DB} ${BACKUP_DIR}

or

1
$ pg_dump -U username -h hostname dbname > dump.sql
$ psql -U username -d dbname -f dump.sql

check repli

coté master

1
postgres=# select client_addr, state, sent_location, write_location,flush_location, replay_location from pg_stat_replication;

coté slave

1
postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Pause de la repli

1
select pg_xlog_replay_pause();

Resume repli

1
select pg_xlog_replay_resume();

Some Tunning

max_connections = 3cores
shared_buffers = 4G < 64G RAM ; 8G > 64G RAM
work_mem = 8M < 32G ram ; 16M < 64G RAM ; 32M > 64G RAM ; if max_connections > 400 => work_mem/2
maintenance_work_mem = 1G
wal_level = hot_standby
archive_mode = on
archive_command = /bin/true
max_wal_senders = 5
wal_keep_segments = 3 checkpoint_segments
random_page_cost = 2.0 for raid, san ; 1.0 for SSD
effective_cache_size = RAM /2
log_min_duration_statement = 1000
log_checkpoints = on

Purge des pg_xlog

1
/etc/init.d/postgresql stop

/usr/lib/postgresql/9.5/bin/pg_controldata /var/lib/postgresql/9.5/main/

sudo -u postgres /usr/lib/postgresql/9.5/bin/pg_resetxlog -o $NEXTOID -x $NEXTXID -f /var/lib/postgresql/9.5/main/

/etc/init.d/postgresql start

Point In Time Recovery
PTR