Postgres

Commands¶

sudo -iu postgres

psql

\help

Docker Dump¶

docker exec -i 'docker_postgres_container' /usr/bin/pg_dump -U 'postgresql_user' 'postgresql_database' > postgres-backup.sql

Upgrade Process¶

mv /var/lib/postgres/data /var/lib/postgres/olddata

mkdir /var/lib/postgres/data /var/lib/postgres/tmp

chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp

[postgres]$ cd /var/lib/postgres/tmp

[postgres]$ initdb -D /var/lib/postgres/data --locale=C.UTF-8 --encoding=UTF8 --data-checksums

[postgres]$ pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

Create Roles¶

CREATE USER thagoat LOGIN SUPERUSER PASSWORD 'password';

CREATE DATABASE db;

Restore Database¶

pg_restore -U thagoat -d ttrss -1 ttrss.psql

Dump Database¶

pg_dump database > db.sql