Notes: Using PostgreSQL

Environment:

Bash v4.2
PostgreSQL v9.5

Remember:

Replace everything that starts with “a_”.

psql

launch psql.

$ sudo su postgres
bash-4.2$ psql
postgres=#

quit.

postgres=# \q

List databases.

postgres=# \l

Create user.

postgres=# CREATE ROLE a_username LOGIN PASSWORD 'a_password';

Change user password.

postgres=# ALTER ROLE postgres WITH PASSWORD 'a_password';

Create database.

postgres=# CREATE DATABASE a_database WITH OWNER a_username;

Drop database.

postgres=# DROP DATABASE a_database;

Drop user.

postgres=# DROP ROLE a_username;

Adding PostGIS extension.

$ sudo su postgres
$ psql -d a_database
a_database=> CREATE EXTENSION postgis;

pg_dump

Backup as dump file (tested to work in migrating servers).

$ pg_dump -Fc -h localhost -U a_username -f a_file.dump a_database

To create a non-compressed backup.

$ pg_dump -h localhost -U a_username -f a_file.sql a_database

To create a schema-only database backup.

$ pg_dump -s -h localhost -U a_username -f a_file.sql a_database

pg_restore

To restore a database from a dump (works with backups as dump only). The database should already exist. If an error occurs for some reason, use postgres as the username.

$ pg_restore -c -h localhost -U a_username -d a_database a_file.dump

To restore a database from a non-compressed backup.

$ psql -h localhost -U a_username a_database < a_file.sql

Re-initialize

To recreate a fresh initdb’d PostgreSQL instance.

$ pg_dropcluster

Querying

Database management commands.

$ psql -h localhost -U a_username -d a_database
sampledb=> select * from a_tablename where id=168;

It works!