PostgreSQL is an open-source relational database system that supports most SQL standards, including foreign keys and transactions. Developed by the community, this database is free, secure, and highly scalable. Many well-known companies' websites use PostgreSQL, such as Apple, Reddit, Spotify, NASA, and more.

This guide will walk you through the installation and setup of PostgreSQL using an Ubuntu 20.04 cloud server as an example. We will create a database, perform some basic database operations, and learn how to create backups and restore them.

Installing PostgreSQL

Update Ubuntu to the latest version.

$ sudo apt -y update

Install the core PostgreSQL database and its dependencies.

$ sudo apt install -y postgresql postgresql-contrib

Verify the installation status of PostgreSQL.

$ dpkg --status postgresql
Package: postgresql
Status: install ok installed
...
Version: 12+214ubuntu0.1

Here are some basic details about PostgreSQL:

• Default port: 5432
• Configuration file: /etc/postgresql/12/main/postgresql.conf
• Database directory: /var/lib/postgresql/12/main
• Service name: postgresql

Perform the following basic operations on PostgreSQL:

Stop PostgreSQL.

$ sudo systemctl stop postgresql

Start PostgreSQL.

$ sudo systemctl start postgresql

Restart PostgreSQL.

$ sudo systemctl restart postgresql

Reload PostgreSQL.

$ sudo systemctl reload postgresql

Check the status of PostgreSQL.

$ sudo systemctl status postgresql

Setting up PostgreSQL

PostgreSQL comes with a command-line tool called psql for logging into the database. It also creates a superuser named "postgres" for accessing PostgreSQL.

Execute the psql tool as the postgres user.

$ sudo -u postgres psql

After executing the above command, the system will display the PostgreSQL prompt, indicating that it is ready to accept SQL commands.

postgres=#

After the initial installation of PostgreSQL, you need to set a password for the postgres user. Execute the following command and enter the password twice.

postgres=# \password postgres
Enter new password:
Enter it again:

Exit the command-line tool.

postgres=# \q

Once the password for the postgres user is set successfully, you can create a test database and perform some basic database operations.

Creating a PostgreSQL database

Create a test database using the CREATE DATABASE command.

postgres=# CREATE DATABASE test_db;

List the created databases.

postgres=# \l

Switch to a database (e.g., test_db).

postgres=#  \c test_db;
test_db=#

You can now create tables within the database.

Creating PostgreSQL tables

Below is an example of creating a simple "customers" table.

test_db-# CREATE TABLE customers (
          customer_id SERIAL PRIMARY KEY,
          first_name VARCHAR (50),
          last_name VARCHAR (50),
          phone VARCHAR (10)
          );

List the created tables.

test_db-# \dt;

View information about a specific table.

test_db=# \d customers;

   Column    |         Type          | Collation | Nullable |                    Default
-------------+-----------------------+-----------+----------+------------------------------------------------
 customer_id | integer               |           | not null | nextval('customers_customer_id_seq'::regclass)
 first_name  | character varying(50) |           |          |
 last_name   | character varying(50) |           |          |
 phone       | character varying(10) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
Press Q to get back to the test_db=# prompt.

Insert data into the table.

test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('JOHN', 'DOE', '11111');
test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('MARY', 'ROE', '33333');
test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('JANE', 'SMITH', '55555');

View data in the table.

test_db=# SELECT * FROM customers;

 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           1 | JOHN       | DOE       | 11111
           2 | MARY       | ROE       | 33333
           3 | JANE       | SMITH     | 55555
(3 rows)

Update data in the table.

test_db=# UPDATE customers SET phone = '88888' WHERE customer_id = 1;

Delete data from the table

test_db=# DELETE FROM customers WHERE customer_id = 2;

Backing up PostgreSQL databases

PostgreSQL provides tools for backing up and restoring databases. To create a compressed backup, use the following command for the test_db database, and enter the password for the postgres user when prompted:

$ pg_dump -d test_db -U postgres | gzip > test_db_backup.sql.gz

To create a SQL-format backup, use the following command, also requiring the postgres user's password:

$ pg_dump -U postgres -f test_db_backup.sql test_db

Restoring PostgreSQL databases

Execute the psql tool as the postgres user.

$ sudo -u postgres psql

Switch to the test_db database, delete the customers table, and then restore the database to verify if the table is recovered.

postgres=# \c test_db;
postgres=# DROP TABLE customers;
postgres=# \q

Restore from a compressed backup.

$ gunzip -c test_db_backup.sql.gz | psql -U postgres -d test_db

Restore from an SQL-format backup.

$ psql -U postgres -d test_db -f test_db_backup.sql

Log in to the database again to confirm whether the customers table has been restored.

$ sudo -u postgres psql

Switch to the test_db database and view the data in the table.

postgres=# \c test_db;
test_db=# SELECT * FROM customers;

 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           3 | JANE       | SMITH     | 55555
           1 | JOHN       | DOE       | 88888
(2 rows)

By following this guide, you have learned how to install and set up PostgreSQL on an Ubuntu 20.04 cloud server, perform basic SQL commands to create databases, and execute CRUD operations. Finally, you have learned how to back up and restore databases using the command-line interface. With a good understanding of PostgreSQL's basic usage, you can use languages like PHP, Node.js, or Python to develop applications or websites that integrate with PostgreSQL.