Postgres

pgbouncer.md

Pgbouncer.ini

[databases]
dbname = host=127.0.0.1 port=5432 
[pgbouncer]
pool_mode = transaction
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/pgbouncer
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, postgres
max_client_conn = 500
default_pool_size = 200
reserve_pool_size = 30

Userlist.txt

"username" "md5b4f2c9a1d7636b3aeb9e1197c61c6987"
"postgres" "md5f82c0b1d146aeb68f00f52e531d5b205"

psql.md

Remove synchronous_standby_names

alter system set synchronous_standby_names =  '';

Backup dumpall

pg_dumpall | gzip -c > all.dbs.out.gz

restore form pg_dump

psql -d doc -f 9.6/dump.sql
or
psql doc < 9.6/dump.sql

Grant all privileges

GRANT ALL privileges ON DATABASE doc TO doc;

Alter database owner

alter database doc owner to doc;

Role superuser

ALTER ROLE doc  superuser;
alter user doc with nosuperuser;
alter user doc with superuser;

Valid privileges

SUPERUSER
NOSUPERUSER
CREATEDB
NOCREATEDB
CREATEROLE
NOCREATEROLE
CREATEUSER
NOCREATEUSER
INHERIT
NOINHERIT
LOGIN
NOLOGIN
REPLICATION
NOREPLICATION
CONNECTION LIMIT connlimit
PASSWORD password
ENCRYPTED
UNENCRYPTED
VALID UNTIL 'timestamp'

Role with login

ALTER ROLE doc  login ;

Set config from psql

alter system set synchronous_commit = 'off';
alter system set synchronous_standby_names = '';
select pg_reload_conf();

Show slots and replication status

select * from pg_stat_replication;
select * from pg_replication_slots;

Current queries sort by runtime

SELECT pid, datname, usename, client_addr, now() - query_start as "runtime", query_start, wait_event, state, query
FROM pg_stat_activity
ORDER BY runtime DESC limit 30;