Postgres

Backup / Restore DB

backup

This will run the dump as the postgres user via su, and avoids some of the permissions errors that can creep up.

# cd /tmp
(just need a writeable directory, aka 777)
# su -c 'pg_dump mydb >> mydb_dump_2020.sql' postgres

assuming you have a postgres admin user, with all rights, this will backup the db. If you are remote, you might need something similar to (localhost/ip, ports, etc specified):

pg_dump -U username -h localhost databasename >> sqlfile.sql

ref: https://stackoverflow.com/questions/37984733/postgresql-database-export-to-sql-file

restore

Now, with that file

# su -c 'psql --set ON_ERROR_STOP=on -f ./mydb_dump_2020.sql' postgres

If you get errors, adjust data as needed or remove the ON_ERROR_STOP (some can be ignored, some can not - use discretion). At least in some cases, you can move data without worrying about postgres versions. I executed a pg_dump from 11.# and imported to 9.6 without issue. Devuan Beowulf to Ascii.

Create a user

• https://wiki.debian.org/PostgreSql

Note that if you have an existing db, you might want to add ownership to this user, or r/w permissions.