How to Install and Set Up PostgreSQL on a Linux Server

PostgreSQL is an open-source object-relational database system known for its stability, extensibility, and performance. But how do you get started with it on a Linux server? We will look at how to install, configure, and secure PostgreSQL on a Linux server.

---

Step 1: Update Your System

First, make sure your system repositories are up to date. Since Ubuntu maintains a list of available versions in the local cache, this step ensures you get the latest stable version when you install a package.

sudo apt update

---

Step 2: Install PostgreSQL

Next, use the apt package manager to install PostgreSQL and contrib utilities:

sudo apt install postgresql postgresql-contrib -y

This command postgresql installs the PostgreSQL database server while postgresql-contrib adds additional features and utilities.

---

Step 3: Verify PostgreSQL is Running

Check whether PostgreSQL is active:

sudo systemctl status postgresql

To enable it at boot and start the service:

sudo systemctl enable postgresql
sudo systemctl start postgresql

---

Step 4: Switch to the Default PostgreSQL User

PostgreSQL creates a default Linux user called postgres. To interact with the database:

sudo -i -u postgres

Access the PostgreSQL Command Line Interface:

psql

This presents something like:

postgres=#

This means you are now inside the PostgreSQL shell.

---

Step 5: Create a New User and Database

While inside the psql prompt, run:

CREATE USER yourusername WITH PASSWORD 'yourpassword';
CREATE DATABASE yourdb;
GRANT ALL PRIVILEGES ON DATABASE yourdb TO yourusername;

Exit the prompt:

\q

---

Step 6: Manage Users

Make sure to change the Password for the Default postgres User

ALTER USER postgres WITH PASSWORD 'test123';

To create a New User, run:

CREATE USER user_1 WITH PASSWORD 'test123';

You can also grant Superuser Privileges using the command:

ALTER USER user_1 WITH SUPERUSER;

Please note that this role has unrestricted access. Therefore, be sure to only grant SUPERUSER to trusted accounts.

---

Step 7: Secure Your PostgreSQL Server

Securing your server is an important step in securing your data and system. You can do this by:

• Disable the postgres user’s remote access.
• Use strong, unique passwords.
• Set up firewalls (e.g., with UFW or iptables).
• Restrict PostgreSQL port (default is 5432) to trusted IPs only.

---

Step 8: Routine operations

Exit from postgres user:

exit

Backup databases regularly using:

pg_dump yourdb > yourdb_backup.sql

---

Conclusion

Your PostgreSQL installation is now live and ready to serve production or development needs.