All HowTo's Linux PostgreSQL Ubuntu, Mint & Debian Linux

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 -d mydatabase -U myRole -W
mydatabase=# CREATE TABLE IF NOT EXISTS person (
    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= 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= mydatabase > backup-$(date +%w).sql

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

Drop (delete) the database:

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

Restore the database:

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

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


PostgreSQL Restore Database


Similar Posts:

Leave a Reply

Your email address will not be published.