How to Set Up a PostgreSQL Database on Ubuntu
PostgreSQL is an outstanding choice for SQL databases on Ubuntu. In this guide, we will guide you through the process of installing and configuring a PostgreSQL database on Ubuntu Server. Whether you’re a newcomer to Linux databases or knowledgeable about PostgreSQL, this step-by-step guide will help you install and configure a database on Ubuntu Server.
How to install PostgreSQL on Ubuntu
Before attempting to install PostgreSQL on your Ubuntu server system, you must update it. Having up-to-date packages for Ubuntu is critical for PostgreSQL system stability.
You’ll first need to run the apt update command to update the packages on your Ubuntu system. This command will refresh the software packages on Ubuntu and check for any updates that can be installed.
sudo apt update
After running the update command, you must install the updates on Ubuntu with the apt upgrade command. The upgrade command should only take a few minutes, and it will install updated packages to your Ubuntu Server system.
sudo apt upgrade
Once everything is up to date, you can install the PostgreSQL package. This package contains everything needed to run PostgreSQL databases on your Ubuntu server. To install the software, use the following apt install command.
sudo apt install postgresql
When you’ve entered the command above, Ubuntu will ask you to enter a password, just like with the update and upgrade commands. Enter your password, then, press the Y button to confirm you wish to install PostgreSQL to Ubuntu.
After pressing the Y button on your keyboard, Ubuntu will download and install the PostgreSQL packages to your system. This process should be quick. When the process is done, you can check the PostgreSQL manual by entering the man postgres command.
man postgres
Or, save the manual to a text file for easier reading.
man postgres > ~/postgres-manual.txt
How to configure PostgreSQL on Ubuntu Server
Now that PostgreSQL is installed on Ubuntu, you must configure it. To start, log into the database system in Ubuntu Server with the postgre user account. You can do this by executing the following sudo -u command.
sudo -u postgres psql
After logging into PostgreSQL with the command above, it is time to create a new database user account. You can accomplish this with the following command. Please be sure to change “new_username” and “new_password” with your desired user and password.
CREATE USER new_username WITH ENCRYPTED PASSWORD 'new_password';
Once the command above is entered, you can run the following command to ensure that your new PostgreSQL database user is available in the system.
SELECT usename FROM pg_user WHERE usename = 'new_username';
With the new database account created, exit the Postgre prompt, as you no longer need to be logged into it. You can log out using the \q command.
\q
You can now create a database. You can do this by executing the In this example, the database we’re creating is called “test.” However, be sure to customize the name to suit your needs when using PostgreSQL.
sudo -u postgres createdb test
After you’ve created the “test” database, you can run the \l command to view your newly created database in the PostgreSQL table.
sudo -u postgres psql -c "\l"
Once you’ve verified that the new database exists in PostgreSQL, it is time to grant your database user privileges to the database. Be sure to change “new_username” to the PostgreSQL database you created previously.
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE test TO new_username;"
To verify that your database user has its privileges, run the following command and check the “Access privileges” column.
sudo -u postgres psql -c "\l"
How to back up a PostgreSQL database
It’s good practice to keep your databases backed up in case anything goes wrong. To back up a PostgreSQL database on your Ubuntu Server, run the following command. be sure to change “database_name” to the name of the PostgreSQL database you wish to export.
sudo -u postgres pg_dump -Fc database_name > backup_file.dump
Once you run the command above, your database will be backed up to “backup_file.dump” on your Ubuntu Server for safe-keeping.
Restoring the backup
At any time if you need to restore your backup (in case of data loss,) you can execute the following pg_restore command. Remember to change “database_name” with the database you are restoring to.
sudo -u postgres pg_restore -d database_name backup_file.dump
