Install PostgreSQL, Create a User/Role, Create a DB, Backup a DB, and Restore a DB

This article walks you through the process of installing PostgreSQL, creating a user, backing up a database and restoring a database. In this tutorial, we’re using Ubuntu, but the commands are the same for everything except the installation.

Install PostgreSQL on Ubuntu:

apt install postgresql postgresql-contrib
systemctl start postgresql
ufw allow 5432

Log into the PostgreSQL database server to create the new user:

# sudo -i -u postgres
$ psql
postgres=# CREATE ROLE myRole;
postgres=# ALTER ROLE myRole PASSWORD 'myPassword';
postgres=# \q

Notice that I’ve included the # and $ for context. PostgreSQL used to have “users” which were associated with “roles” which had permissions to “databases”. Now days, we can login as “roles”.

Create a new database called “mydatabase” (databases must be lower case):

# sudo -i -u postgres
$ psql
postgres=# CREATE DATABASE mydatabase;
postgres=# \q

TIP: The “\l” command (within PostgreSQL) lists the databases.

Grant full access to the “myRole” user to the “mydatabase” database:

# sudo -i -u postgres
$ psql
postgres=# GRANT CONNECT ON DATABASE mydatabase TO myRole;
postgres=# \q

Confirm our new DB and access by sending a simple SQL query:

# psql -h 127.0.0.1 -d mydatabase -U myRole -W
mydatabase=# CREATE TABLE IF NOT EXISTS person (
    Id 	INT PRIMARY KEY,
    first_name VARCHAR(50)
);
mydatabase=# INSERT INTO person(Id, first_name) VALUES ('1','Kym');
mydatabase=# SELECT * FROM person;
mydatabase=# \q

\q quit/exists the database command line interface. In the above section, we connected as the myRole user to confirm it works.

Backup a PostgreSQL database:

$ pg_dump --inserts --column-inserts --username=myRole --host=127.0.0.1 mydatabase > backup-$(date +%w).sql

The above will not prompt for a password if executed from the localhost.

Backup a PostgreSQL database from another host:

$ PGPASSWORD='myPassword' pg_dump --inserts --column-inserts --username=myRole --host=10.1.2.3 mydatabase > backup-$(date +%w).sql

Where “10.1.2.3” is the IP address of the remote PostgreSQL database server.

Drop (delete) the database:

$ psql -h 127.0.0.1 -d mydatabase -U myRole -W
mydatabase=# DROP DATABASE mydatabase;
mydatabase=# \q

Restore the database:

# sudo -i -u postgres
$ psql
postgres=# CREATE DATABASE mydatabase;
\q
$ psql -d mydatabase -f . backup-3.sql

TIP: Connect to the database “psql -h 127.0.0.1 -d mydatabase -U myRole -W” and then run a query “SELECT * FROM person” to confirm you have restored the database successfully.

Resources:
https://simplebackups.com/blog/the-ultimate-postgresql-database-backup-script/#database-backup-script-for-postgresql-and-dumping-to-amazon-s3

PostgreSQL Restore Database

PostgreSQL INSERT

https://www.tutorialsteacher.com/postgresql/create-tables
https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database