Skip to content

Postgresql

https://wiki.ubuntuusers.de/PostgreSQL/

Install

Install issues

If no cluster gets created automatically, kick it with:

pg_createcluster 9.4 main --start

Authentication

Connect as postgres (admin) user

sudo -i -u postgres
psql

Bitnami K8S image

psql -U postgres

You will be prompted to enter the postgres user password. This is the same as the application password.

Connect as different user

psql user_name  -h 127.0.0.1 -d db_name

Change

Usage

Show DB:

\l

Use db:

\c openproject;

Show tables:

\dt

Query data:

SELECT * FROM users;

List postgres users:

\du

Show hashed passwords:

select * from pg_shadow;

Upgrade postgresql cluster

Manual

https://scottlinux.com/2015/11/14/upgrade-postgresql-on-debian-jessie-with-pg_upgradecluster/

apt-get install postgresql-9.4
systemctl stop postgresql@9.4-main.service apache2 foreman
sudo -i -u postgres
  pg_dropcluster --stop 9.4 main
  pg_ctlcluster 9.4 main stop
  exit
systemctl start postgresql@9.4-main.service apache2 foreman

when everything is ok:

apt remove postgresql-9.1 postgresql-client-9.1  --purge

Docker upgrade

Upgrading in Kubernetes

Bitnami chart

Backup & Restore

Kubernetes

With this, you can use pg_restore with more options (like clean all DB objects before restoring).

Backup:

sudo -u postgres pg_dump --format=custom dbname > filename

Restore:

DROP DATABASE openproject;
CREATE DATABASE openproject;
pg_restore -d openproject /var/backups/postgres/openproject.pg_dump

Unfortunately, this didn't work:

pg_restore --create --clean -d openproject /var/backups/postgres/openproject.pg_dump

Convert custom dump format to sql

pg_restore openproject-2017-02-09-13\:17\:25.dump > openproject-2017-02-09-13:17:25.sql

Using plain sql dump format

https://www.postgresql.org/docs/9.4/static/backup-dump.html

Backup:

pg_dump dbname > outfile

Restore:

psql dbname < infile
psql --set ON_ERROR_STOP=on dbname < infile

Storing dumps in version control

Usage:

git clone https://github.com/akaihola/pgtricks.git
cd path/to/git/repo
pg_dump --clean openproject > openproject.sql
../pgtricks/pgtricks/pg_dump_splitsort.py openproject.sql

Etc

Rewriting string in all tables using dump/restore

systemctl stop openproject-puma.service nginx.service

su - postgres
pg_dump --clean openproject > openproject.before_path_rewrite.sql

Change and review:

sed 's/dokuwiki-alt/it\/dokuwiki-media/g' \
  openproject.after_path_rewrite.sql > openproject.after_path_rewrite.sql
git diff --word-diff=color --word-diff-regex=. \
  openproject.before_path_rewrite.sql openproject.after_path_rewrite.sql

Restore:

psql --set ON_ERROR_STOP=on openproject < openproject.after_path_rewrite.sql

Upgrade postgres deployed by Bitnami helm chart

Steps:

Create new helm release of Postgres while the old one running Migrate:

 kubectl exec -it new-helm-db-postgresql-0 -- bash \
   -c 'export PGPASSWORD=${POSTGRES_PASSWORD}; \
       time pg_dump -h old-postgresql -U postgres | psql -U postgres'

pgloader

Migrate to PostgreSQL in a single command!

Install pgloader

  • Alpine: apk add pgloader