Skip to content

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;