Setup
It’s probably best to have the postgres user own everything (this user is automatically created when installing the postgresql package on Arch Linux).
# Initialise the database cluster. Run as postgres user.
initdb --locale=C.UTF-8 --encoding=UTF8 -D '/var/lib/postgres/data'
Backups
There are three options for performing backups. The best choice by far is the pg_dump option.
The /var/lib/postgres/data directory can be backed up directly, but the server must not be running, and the backup can only be restored onto the exact version of postgres that created it.
SQL dump
Generates a file containing SQL commands that will generate and populate the database from scratch. Uses the pg_dump program. The benefit of this approach is that the backup file can be restored into a Postgres server running a different version. This can be used with no issues on a live database.
pg_dump dbname > dumpfile
Use the -h host and -p port options to specify a database server. Will connect using the database username that is equal to the operating system username by default, unless -U is used.
To restore the backup, run the following command. The database dbname won’t be created by this command, it must already exist. A pristine database can be instantiated from template0. The -X flag ensures that psql runs with default settings. The -1 flag will run in single-transaction mode, so that any errors will roll back the database instead of leaving a broken mess.
createdb -T template0 dbname psql -X dbname < dumpfile
The backup can be compressed using gzip:
pg_dump dbname | gzip > filename.gz gunzip -c filename.gz | psql dbname
Usage
Use psql from the postgres account to bypass access controls. Quit with \q.
Read commands from file
Use \i file/path.sql to read in and execute commands from a file, or pipe it into psql.
cat commands.sql | psql dbname
List tables
Use \dt in psql.