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
https://www.tutorialsteacher.com/postgresql/create-tables
https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database