Postgres: maintenance

Scope: maintain the the database

Export a full database

Create Dump

# --no-owner no ownership in output
pg_dump dmsdb --file=/data/postgresql/dumps/2019-05-08.dump.sql
   --format=plain --encoding=UTF8 --schema=blinkdms_tab
   -x --no-tablespaces --no-owner

Restore a dump

Scope: restore a dump for an existing DB-user

• delete old data of DB-user

• create new database table

• import DUMP

delete old data:

psql -d dmsdb
sql> DROP SCHEMA blinkdms_tab cascade;
sql> REASSIGN OWNED BY blinkdms TO postgres;
sql> DROP OWNED BY blinkdms;
sql> DROP USER blinkdms;
sql> DROP ROLE blinkdms_user;
sql> exit;

now recreate the DB user + tablespace:

psql -d dmsdb -v ON_ERROR_STOP=1
  < /opt/blinkdms/blinkdms/install/sql/create_user.sql

import:

psql -v ON_ERROR_STOP=1 -d dmsdb -U blinkdms
  < /data/postgresql/dumps/YOUR_DUMP.sql