PostgreSQL is an open-source Object-Relational Database Management System (ORDBMS) that supports most SQL standards, including foreign keys and transactions.
This community-driven database server is free to own, reliable, secure, and scalable. It powers dynamic websites and applications at Apple, Reddit, Spotify, and NASA.
In this tutorial, you'll install and configure PostgreSQL server on Ubuntu 20.04 LTS, then create a database and perform data manipulation. You'll learn how to safeguard the security of your database by creating and restoring backups.
Before you begin, make sure you have the following:
An Ubuntu 20.04 LTS server.
Start by updating your Ubuntu's server package information index.
$ sudo apt -y update
Then, install the PostgreSQL core database server, command-line client, and additional dependencies.
$ sudo apt install -y postgresql postgresql-contrib
Verify the PostgreSQL installation.
$ dpkg --status postgresql
Make sure your output is similar to the one shown below. You should have PostgreSQL version 12.
Package: postgresql
Status: install ok installed
...
Version: 12+214ubuntu0.1
...
PostgreSQL runs on port 5432.
The default configuration file is located here:
/etc/postgresql/12/main/postgresql.conf
PostgreSQL creates all databases in this directory:
/var/lib/postgresql/12/main
The PostgreSQL database server runs as a service under the name postgresql. Manage the service by running the commands below.
Stop PostgreSQL server:
$ sudo systemctl stop postgresql
Start PostgreSQL server:
$ sudo systemctl start postgresql
Restart PostgreSQL(e.g. after changing configuration settings) server:
$ sudo systemctl restart postgresql
Reload PostgreSQL server:
$ sudo systemctl reload postgresql
Check PostgreSQL status:
$ sudo systemctl status postgresql
You've successfully installed the PostgreSQL database server. Before you begin using it, you'll secure the root user with a password in the next step.
By default, PostgreSQL ships with psql. This is a command-line client that you can use to log in to the database server. The installation setup also creates a UNIX user named postgres. This is the PostgreSQL server super-user or root user.
To log in to the PostgreSQL server via the psql command-line client as the postgres user, use the command below.
$ sudo -u postgres psql
Under the hood, the statement above switches to the postgres UNIX USER and runs the psql command. Once you execute the command, you will get the PostgreSQL prompt below. This means your PostgreSQL server is ready to receive SQL commands.
postgres=#
When you install PostgreSQL for the first time, a password for the super-user is not set by default. To create the password, execute the statement below.
postgres=# \password postgres
You will get the prompt below. Enter a strong password and confirm it.
Enter new password: EXAMPLE_PASSWORD
Enter it again: EXAMPLE_PASSWORD
Output.
postgres=#
Then, exit from the psql command-line client.
postgres=# \q
To test the new password, edit the /etc/postgresql/12/main/pg_hba.conf configuration file using nano.
$ sudo nano /etc/postgresql/12/main/pg_hba.conf
Locate the line local all postgres peer as shown in the below content.
...
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
...
Change the authentication method from peer to md5 so that the line reads local all postgres md5.
...
# Database administrative login by Unix domain socket
local all postgres md5
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
...
Save and close the file by pressing CTRL + X, then Y and ENTER. Then, restart the postgresql service to load the new settings.
$ sudo systemctl restart postgresql
Try to log in to the PostgreSQL server again; this time around, you should be prompted to enter a password.
$ sudo -u postgres psql
Enter the PostgreSQL server password that you created earlier and press ENTER to continue. You should now be logged in to the PostgreSQL server. Make sure you get the command-line client prompt as shown below.
postgres=#
Once you've protected your PostgreSQL server with a password, you can now create a sample database and perform some data manipulation on it.
Use the CREATE DATABASE command to create your first test_db database.
postgres=# CREATE DATABASE test_db;
Output.
CREATE DATABASE
To list the databases, use the \l command.
postgres=# \l
Your new test_db database should be listed as shown below.
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
...
test_db | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
To switch to the test_db database, use the \c command.
postgres=# \c test_db;
The syntax below should also work.
postgres=# \connect test_db;
Ensure you get an output showing that you've been connected to the test_db database.
You are now connected to database "test_db" as user "postgres".
test_db=#
This means your test_db database is ready, and you can proceed to create a table in the next step.
Create your first table named customers under the test_db database. Use the SERIAL statement to create an auto-increment column to store the customer_id's.
test_db-# CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
phone VARCHAR (10)
);
Output.
CREATE TABLE
Run the \dt command to list PostgreSQL tables.
test_db-# \dt;
Your customers table should now be present on the list of relations, as shown below.
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | customers | table | postgres
(1 row)
To get a description of the customers table, use the `\ d' command.
test_db=# \d customers;
You can now see the structure of your customers table as shown below.
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.
Run the SQL commands one by one to insert sample data into the customers 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');
Ensure you get the output below after executing each INSERT statement.
INSERT 0 1
...
Run a SELECT statement against the customers table to display the records that you've inserted.
test_db=# SELECT * FROM customers;
You will get a list of customers as shown below.
customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
1 | JOHN | DOE | 11111
2 | MARY | ROE | 33333
3 | JANE | SMITH | 55555
(3 rows)
To edit the data in a PostgreSQL table, use the UPDATE and WHERE statements together. For instance, to update JOHN DOE's phone to 88888 from 11111, execute the command below.
test_db=# UPDATE customers SET phone = '88888' WHERE customer_id = 1;
Output.
UPDATE 1
Confirm if the UPDATE statement was executed successfully by running a SELECT statement against the record.
test_db=# SELECT * FROM customers WHERE customer_id = 1;
As you can see from the output below, JOHN DOE's phone number has been updated to 88888.
customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
1 | JOHN | DOE | 88888
(1 row)
Execute the DELETE statement to delete a record in a PostgreSQL database table. For instance, to delete MARY ROE from the customers table, use the command below.
test_db=# DELETE FROM customers WHERE customer_id = 2;
Output.
DELETE 1
To confirm the deletion, issue the SELECT statement against the customers table.
test_db=# SELECT * FROM customers;
You can now see that MARY ROE's record is missing from the table.
customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
3 | JANE | SMITH | 55555
1 | JOHN | DOE | 88888
(2 rows)
Exit from the PostgreSQL command-line interface.
test_db=# \q
In the next step, you'll learn how to backup and restore a PostgreSQL database.
PostgreSQL comes with useful tools for creating backups and restoring databases from dump files.
To create a compressed backup for your test_db, use the pg_dump utility.
$ pg_dump -d test_db -U postgres | gzip > test_db_backup.sql.gz
When prompted, enter the super-user password for your PostgreSQL database server and press ENTER to proceed. The pg_dump should finalize dumping and compressing the database.
Create a plain-text backup by executing the command below.
$ pg_dump -U postgres -f test_db_backup.sql test_db
Again, key in the password for your PostgreSQL server and press ENTER to continue. A plain text SQL file should be created. You can confirm the creation of the above backup files by listing the current files from your working directory.
$ ls -lsa
Your backup files should be listed as shown below.
...
... test_db_backup.sql
... test_db_backup.sql.gz
...
After creating the dump files, you will restore your database to its original state in the next step.
Log in to the PostgreSQL database server as a super-user.
$ sudo -u postgres psql
When prompted, enter your password and press ENTER to proceed. Next, switch to the test_db and drop the customers table that you created earlier. You'll try to recreate this table again from the backups.
postgres=# \c test_db;
postgres=# DROP TABLE customers;
Then, exit from the PostgreSQL command-line interface by executing the \q command.
postgres=# \q
Next, issue either of the commands below to restore the database to its original state from the compressed backup file.
Restore from compressed backup:
$ gunzip -c test_db_backup.sql.gz | psql -U postgres -d test_db
Restore from plain text SQL file that you created ealier.
$ psql -U postgres -d test_db -f test_db_backup.sql
Key in your password and press ENTER to continue. Your database should now be restored from either of the backup files. You can log in to the database server again to check if the customers table has been recreated.
$ sudo -u postgres psql
Enter your password and press ENTER to proceed. Then switch to the test_db.
postgres=# \c test_db;
Try querying data from the customers table
test_db=# SELECT * FROM customers;
You will get a list of customers as shown below.
customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
3 | JANE | SMITH | 55555
1 | JOHN | DOE | 88888
(2 rows)
This means the backup has been restored successfully.
In this guide, you've learned how to install and configure PostgreSQL on Ubuntu 20.04. You've also used the basic SQL syntax to create a PostgreSQL database and performed some CRUD operations. Towards the end, you've used data management solutions to backup files and restore your database. With this PostgreSQL step up in place, you can now use your favorite scripting language like PHP, Node.js, or Python to create your web application or website.