PostgreSQL

List databases

sudo -u postgres psql -l
sudo -u postgres psql -c "\list+"

List tables in the database

psql -U postgres
\l
\c database-name
\dt

Backup dump database

su -c "pg_dump -U postgres --create -d mydb > db.sql" postgres

PostgreSQL backup all databases in separate files

su - postgres
mkdir /tmp/backup-$(date +"%d-%m-%Y")
for DB in $(psql -t -c 'SELECT datname FROM pg_database WHERE datistemplate = false;'); do pg_dump --create "$DB" > "/tmp/backup-$(date +"%d-%m-%Y")/${DB}.sql"; done

Restore from file to the database

psql demo < /tmp/demo_backup.sql

PostgreSQL restore all databses from files

su - postgres
for DB in /tmp/backup-$(date +"%d-%m-%Y")/*.sql; do psql < "$DB"; done

Terminate transactions, with duration more than 1000 seconds

select pg_terminate_backend(pid)
from pg_stat_activity
where state != 'idle' and backend_type = 'client backend' and extract(epoch from now() - xact_start) > 1000;

Install PostgreSQL on YUM

yum install postgresql postgresql-server postgresql-devel postgresql-libs postgresql-contrib

установим переменные для PostgreSQL

nano /root/.bash_profile

PGDATA=/var/lib/pgsql/data
PATH=$PATH:/usr/bin/postgres
PGUSER=postgres
export PGDATA PATH PGUSER

/etc/rc.d/init.d/postgresql initdb

nano /var/lib/pgsql/data/postgresql.conf 

listen_addresses = '*' 
log_line_prefix = '%t %u %d'

chkconfig postgresql on
/etc/rc.d/init.d/postgresql start

su - postgres
psql postgres postgres
alter user postgres with password 'password';
\password postgres 
CREATE USER zabbix WITH PASSWORD 'password';
CREATE DATABASE zabbix OWNER zabbix;
\q

nano /var/lib/pgsql/data/pg_hba.conf

local   all      all                            md5
host    all      all    127.0.0.1/32            md5

cd /home/zabbix/zabbix-2.0.3/database/postgresql
cat schema.sql | psql -U zabbix zabbix 
cat images.sql | psql -U zabbix zabbix
cat data.sql | psql -U zabbix zabbix

Create database user and database

sudo -u postgres createuser --pwprompt zabbix
sudo -u postgres createdb -O zabbix zabbix_proxy

PostgreSQL database backup in container

Do automatic database backup in Docker container.

Create container with volume mapped to /backups in container. Backup archives will be available in that local folder.
```
volumes:
   - ./container_db/backups:/backups
```

Create /opt/container/backup.sh

#!/bin/bash
if [ "$(docker inspect -f '{{.State.Running}}' container_db 2>/dev/null)" = "true" ]; then docker exec container_db /backups/autopostgresqlbackup; fi

Create /opt/container/container_db/backups/autopostgresqlbackup

Install: apt install autopostgresqlbackup
If the default options are not suitable for you, change them: ${EDITOR} /etc/default/autopostgresqlbackup
https://github.com/k0lter/autopostgresqlbackup/blob/master/Documentation.md

Create /etc/cron.d/container_db_docker_psql_backup
```
0 4 * * * root /opt/container/backup.sh
```

Docker Compose healthcheck for PostgreSQL container

healthcheck:
   test: ["CMD-SHELL", "pg_isready -U postgres"]
   interval: 5s
   timeout: 5s
   retries: 5

To check if PostgreSQL server is running in standalone mode

sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

If there are no rows in result, your PostgreSQL server is running in standalone mode. If there is at least one row in the result (each row represents one replica), your PostgreSQL server is a master and currently connected to a replica(s). That's characteristic for a master/replica setup.

To check if your PostgreSQL server is a replica

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

If the result is t (true), your PostgreSQL server is running as a replica. If the result is f (false), it is not running as a replica (it could be a master or a standalone server).

PostgreSQL list users

SELECT * FROM pg_authid;

PostgreSQL show all databases

sudo -u postgres psql -t -c 'SELECT datname FROM pg_database WHERE datistemplate = false;'

PostgreSQL WAL replication

If PostgreSQL replication is enabled then WAL archiving should be enabled. PostgreSQL by itself WILL NO delete any WALs generated, what allows unlimited growth of pg_wal directory:

 show wal_level;
 wal_level 
-----------
 replica

show archive_command;
 archive_command 
-----------------
 (disabled)

PostgreSQL total number of current database connections

sudo -u postgres psql
SELECT count(*) FROM pg_stat_activity WHERE datname = 'bitbucket';

PostgreSQL get details of all connections

sudo -u postgres psql
SELECT pid, usename, application_name, client_addr, state, backend_start, xact_start, query_start, state_change, query
FROM pg_stat_activity
WHERE datname = 'bitbucket'
ORDER BY query_start;

PostgreSQL identify slow queries:

sudo -u postgres psql
SELECT pid, usename, application_name, client_addr, state, backend_start, xact_start, query_start, state_change, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE datname = 'bitbucket' AND state = 'active'
ORDER BY duration DESC;

When you run a SELECT query in psql, the output is paged by default using a pager like less if the resultset is large. The pager allows you to scroll through the output, but once you exit the pager, you return to the psql prompt and the output is no longer visible.

You can disable the pager for the current session by using the \pset command within psql:

psql -U username -d databasename
\pset pager off

PostgreSQL List all columns from all tables

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema = 'public'
ORDER BY table_name, column_name;

PostgreSQL List primary keys

SELECT
 tc.table_schema, 
 tc.table_name, 
 kcu.column_name
FROM 
 information_schema.table_constraints AS tc 
 JOIN information_schema.key_column_usage AS kcu
 ON tc.constraint_name = kcu.constraint_name
WHERE 
 constraint_type = 'PRIMARY KEY'
 AND tc.table_schema NOT IN ('pg_catalog', 'information_schema');

PostgreSQL List foreign keys

SELECT
 tc.constraint_name,
 tc.table_name,
 kcu.column_name,
 ccu.table_name AS foreign_table_name,
 ccu.column_name AS foreign_column_name
FROM
 information_schema.table_constraints AS tc
 JOIN information_schema.key_column_usage AS kcu
 ON tc.constraint_name = kcu.constraint_name
 JOIN information_schema.constraint_column_usage AS ccu
 ON ccu.constraint_name = tc.constraint_name
WHERE
 constraint_type = 'FOREIGN KEY'
 AND tc.table_schema NOT IN ('pg_catalog', 'information_schema');

PostgreSQL List indexes

SELECT
  t.relname AS table_name,
  i.relname AS index_name,
  a.attname AS column_name
FROM
  pg_class t
  JOIN pg_index ix ON t.oid = ix.indrelid
  JOIN pg_class i ON i.oid = ix.indexrelid
  JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
  JOIN pg_namespace n ON t.relnamespace = n.oid
WHERE
  n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY
  t.relname,
  i.relname;