PostgreSQL Cheat Sheet

2017-09-06 1 minute read 0 Comments improve this post #command line | #database | #linux | #postgresql

Logging in:
Create a user
Backup
Restore
Check Wether a Specific Database Exists
Check Whether a Specific Table Exists

Logging in:

$ psql -h localhost -U postgres <database[optional]>

List all databases (all commands can be administered after logging into postgreSQL):

\l
# or
\list

Create a user

CREATE USER <username> PASSWORD '<password>';

Backup

Create Backup User

CREATE USER <backup username> SUPERUSER PASSWORD '<password>';
ALTER USER <backup username> set default_transaction_read_only = on;

Setup homedir so password does not prompt

$ touch ~/.pgpass
$ chmod 0600 ~/.pgpass
$ echo "<hostname>:<port>:<database>:<username>:<password>" > ~/.pgpass

Each respective placeholder above (ie. ) could either be a literal value or a *, which matches anything.

REF: https://www.postgresql.org/docs/current/static/libpq-pgpass.html

Dump all databases

$ pg_dumpall > outfile
$ pg_dumpall | gzip > outfile.gz

Dump specific databaser

$ pg_dump <database> > outfile

Restore

$ pg_restore -d <database> <filename>

Check Wether a Specific Database Exists

sql="SELECT 1 FROM pg_database WHERE datname='<database_name>';"

if [ "$( psql -tAc "${sql}" )" = '1' ]
then
    echo "Database already exists."
else
    echo "Database does not exist."
fi

Check Whether a Specific Table Exists

sql="SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name='<table_name>';"

if [ "$( psql -tAc "${sql}" )" = '1' ]
then
    echo "Table already exists."
else
    echo "Table does not exist."
fi

improve this post

Categories: #database | #linux