If Ubuntu is part of your work environment, there will be no trouble deploying a PostgreSQL database server on it. With this manual at hand, you will promptly learn everything from installing PostgreSQL on your Ubuntu machine to managing it with a high-end PostgreSQL GUI client.
PostgreSQL has earned quite a reputation as a stable and reliable open-source database system with a rich feature set and high performance, steadily perfected over decades of development. Like any other top-tier database system, PostgreSQL has a large, dedicated community that keeps contributing to its high levels of resilience, integrity, and correctness. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying; it is a popular choice for web, mobile, geospatial, and analytics applications.
Ubuntu is a free and open-source operating system and hands down the most popular Linux distribution, developed by the British company Canonical and a community of other developers. It is also one of the most viable platforms for MySQL and PostgreSQL database servers.
Long-term releases of Ubuntu are published every two years, and upgrades are released every six months. As of late 2024, the most recent releases are 20.04, 22.04, and 24.04.
The simplest way to install PostgreSQL is to retrieve it as a package from the Ubuntu repository. It can be done from the command line - namely, using the Advanced Package Tool (APT). The flow is as follows:
1. Open a terminal window on your Ubuntu machine and run the following command to install the latest stable PostgreSQL version:
sudo apt install postgresql
If you want to install an older version, mention it in the same command.
sudo apt install postgresql-15
You may want to install additional extension modules (by adding postgresql-contrib to the command) and the PostgreSQL CLI (by adding postgresql-client to the command).
sudo apt install postgresql postgresql-contrib postgresql-client
2. When the installation is completed, you can check whether PostgreSQL is running without errors. To do that, execute the following command.
systemctl status postgresql
You can also check the port. By default, PostgreSQL listens to 5432 for incoming connections.
ss -nlt
3. Here are a few additional commands to manage your newly installed PostgreSQL.
Alternatively, you can install PostgreSQL from its official repository; it is updated more often than other sources, so you can rest assured you'll be getting the latest version.
1. Open a terminal window on your Ubuntu machine. First, you need to install prerequisite packages that are required to install software certificates for a secure SSL connection.
sudo apt install wget ca-certificates
2. Next, you need to create a file with the following path: /etc/apt/sources.list.d/pgdg.list. This file will contain the address of the official PostgreSQL repository specific to your Ubuntu server version.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
3. Now, run the following command to add the PostgreSQL GPG key to your package manager. APT will apply this key to verify the packages in the repo. If everything goes successfully, you will see an OK response.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
4. Update the package lists so that APT knows where it can find the official PostgreSQL packages.
sudo apt update -y
5. Install PostgreSQL alongside extension modules (postgresql-contrib) and the PostgreSQL CLI (postgresql-client). Wait until the installation is completed.
sudo apt install -y postgresql postgresql-contrib postgresql-client
6. Afterwards, you can check the version you've installed by running the following:
sudo dpkg --status postgresql
7. When all is said and done, you can start your newly installed PostgreSQL with the following command:
sudo systemctl start postgresql
Finally, you can verify its status, which should be shown as active.
sudo systemctl status postgresql
sudo systemctl start postgresql.service
sudo systemctl status postgresql.service
1. If you don't need to install the PostgreSQL server locally, and you're only looking for the command-line client to connect to a remote server, you can install it via the same APT using the following command:
sudo apt install postgresql-client
2. Afterwards, you can use the installed client to connect to the required server/database from the command line. This is how it's done for a database on a localhost server:
psql -h localhost -d database_name -U database_user
Simply replace database_name and database_user with the corresponding database and user names, and there you have it. Before you create new users and databases, you need to start with the default user postgres and default database postgres, created during the installation of PostgreSQL.
Similarly, this is how it's done for a remote server:
psql -h server_ip_address -d database_name -U database_user
Now that we've shown you how to install and start a PostgreSQL server, let's cover a few more crucial actions such as checking the status, logging in, and securing the default user with a password.
You can check the status of PostgreSQL at any moment by running the following command:
sudo systemctl status postgresql
This command will display the information about the PostgreSQL service, including whether it is active (running), inactive, or in a failed state. If you are using a version of Linux that uses systemd for managing services, this is the most common way to check the status.
If your system does not use systemd, you might use the following alternative command:
sudo service postgresql status
You can also check the port using ss -nlt. By default, it's 5432.
When you install a PostgreSQL server, an admin user postgres is created by default. Use it to log in to PostgreSQL for the first time:
sudo -u postgres psql
The postgres user does not have a password by default, so you can set it manually with the \password command. You will be prompted to enter your new password twice. After you set it, run \q to exit.
1. By default, your newly installed PostgreSQL server will listen only to your localhost; if you need to enable remote access, you will need to modify the configuration file, either limiting access to specified IP addresses or opening it for everyone. This is the file in question, and you can modify it with the nano editor.
sudo nano /etc/postgresql/16/main/postgresql.conf
2. In the configuration file, find the listen_addresses line, remove the # character that goes before it, and replace localhost with the * character. This will allow all IP addresses to connect to your newly installed PostgreSQL server.
In case you want to allow only specific IP addresses, enter them instead of the * character, separated by commas.
Afterwards, save the file by pressing Ctrl+X, Y, and then Enter.
3. Now you need to edit another configuration file in a similar way.
sudo nano /etc/postgresql/16/main/pg_hba.conf
In this file, change the host value as shown in the following example:
host all all 0.0.0.0/0 host all all ::0/0
Like in the previous case, save the file by pressing Ctrl+X, Y, and then Enter.
4. Next, you need to apply these changes by restarting your PostgreSQL server.
sudo systemctl restart postgresql
5. Now you can double-check whether PostgreSQL is listening to all addresses. To do that, run the following:
sudo apt install net-tools sudo netstat -antup | grep 5432
The output should be as follows:
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN tcp6 0 0 :::5432 :::* LISTEN
6. The final step depends on whether your firewall is turned on or off.
sudo ufw allow 5432
After running this command, you can check the status of ufw to confirm that the rule has been applied:
sudo ufw status
So, with all the installation and configuration out of the way, now it's time to start creating new databases and managing actual data. Here, you can't go wrong with a GUI-powered PostgreSQL client application that is far more convenient, intuitive, and versatile than a simple command line.
The client in question is called dbForge Studio for PostgreSQL, and it's got quite a few features that will help you connect to your databases, migrate, compare, and synchronize them, write and format SQL code, and perform a slew of operations with your data, from simple retrieval and editing to import, export, aggregation, and analysis. The Studio is available for a free 30-day trial, which is hopefully quite enough to explore the diversity of its features and see how well it addresses your needs.
Once you open the newly installed dbForge Studio for PostgreSQL on your Ubuntu machine, your first step will be to establish a connection to your database. The Studio will suggest it automatically by opening the Database Connection Properties window. Alternatively, you can access it at any moment from the Database menu > New Connection.
1. Here, you need to enter the corresponding connection properties: Host, Port, User, Password, and Database—all according to the way you configured them during the installation.
2. Optionally, you can click Test Connection. If everything has been entered correctly, you will see a corresponding message. Then click Connect, and there you go.
Like we said, the default postgres user has been created during the installation. However, you might need to create more users and provide them with respective privileges. In the Studio, it's done extremely easily. Just click New SQL on the toolbar, write a CREATE ROLE command, and run it.
To learn more about the parameters that can be used in your CREATE ROLE command, refer to the corresponding topic from the official PostgreSQL documentation.
Quite similarly, you can run the CREATE DATABASE and DROP DATABASE commands, enhancing them with the available parameters. The intuitive GUI and smart code completion will make it all much simpler and faster for you.
With the Studio at hand, you can export PostgreSQL databases to 14 different data formats with ease.
1. First, proceed to the Database menu and select Tasks > Import Data.
2. You will be greeted by the Data Export wizard, whose first page provides you with 14 formats to choose from: HTML, TXT, XLS, XLSX, MDB, RTF, PDF, JSON, XML, CSV, ODBC, DBF, SQL, and Google Sheets. Pick the one you need and move on.
3. Next, the wizard will guide you through a number of pages where you'll be able to configure the export procedure to your requirements. These pages include Source, Output settings, Options, Data formats, Page print settings, Exported rows, and Errors handling. The availability and content of each page may vary according to the selected format.
4. Once you have configured your settings, you can simply click Export, and that's it. Your data will be exported in a matter of seconds. Note that you can do it at any moment, even without configuring anything.
5. You can save your configured settings as a template and reuse it whenever it's necessary. To do that, click Save in the lower-left corner of the wizard, and then select Save Template. You will be able to reuse it during your following export operations, eliminating the need to configure your settings anew.
6. Finally, you can set up automated export from the command line. To do that, configure your settings and click Save in the lower-left corner of the wizard, and then select Save Command Line. In the window that opens, you will see the auto-generated script; you will be able to save it as a .bat file and run it whenever you need.
Similarly, you can set up and automate the import of PostgreSQL data from external sources into your databases. The flow is basically the same. You go to the Database menu > Tasks > Import Data and follow the Data Import wizard page by page, depending on the selected format.
dbForge Studio for PostgreSQL provides you with tools for more complex operations like data analysis and reporting. For instance, you can create easily readable in-depth reports based on your data (or the output of custom queries). This functionality is available from the Database menu > Report Designer. In the Data Report Wizard that opens, you can pick the required report type. To learn more about each type, refer to PostgreSQL reporting and visualization tools.
After selecting all the necessary data by executing a query and navigating to the output, you can quickly create a pivot table. To access this functionality, use Cross Tab in the Toolbox panel: simply drag it into your report. Once an empty pivot table template is created, you can drag the required fields into the corresponding parts of this table to fill it with data. To learn more about pivot tables, refer to Creating pivot tables in PostgreSQL.
Finally, you can export your report to the format of your choice, including PDF, HTML, MHT, RTF, XLS, XLSX, CSV, text files, and image files. To do that, proceed to the Export Document button on the document toolbar. Next, select the format, specify the export settings, and launch the operation.
If you don't need PostgreSQL on your Ubuntu machine anymore, you can run the following command to remove it:
sudo apt remove postgresql*
If you have multiple PostgreSQL versions, you can uninstall a specified version:
sudo apt remove postgresql-15*
Finally, you can uninstall the PostgreSQL CLI client by running the following:
sudo apt remove postgresql-client
We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.
