How to Install PostgreSQL on Linux

1 - Add the PostgreSQL Yum repository to your Linux distro

To begin with, make sure your system is up-to-date:

sudo yum update

Then, install a repository configuration package using the official PostgreSQL repository for the desired Linux distro.

For CentOS 7 and Red Hat Enterprise:

sudo yum install 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-l
atest.noarch.rpm

For CentOS 8:

sudo yum -y install 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-l
atest.noarch.rpm

For Fedora:

sudo dnf -y install 
https://download.postgresql.org/pub/repos/yum/reporpms/F-37-x86_64/pgdg-fedora-repo-l
atest.noarch.rpm

2 - Install PostgreSQL

To install the PostgreSQL server, use the following command:

sudo yum install postgresql14-server

Before you can use your Postgres databases, you need to create a new database cluster. It comprises a collection of databases that are managed by a single server instance. Create a new PostgreSQL database cluster with the following command:

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

Now you can start and enable PostgreSQL using the following commands:

sudo systemctl start postgresql-14
sudo systemctl enable postgresql-14

To switch to the postgres account on your server, execute the following command:

sudo -i -u postgres

To access the PostgreSQL prompt, type:

psql

This will log you into the PostgreSQL prompt where you can interact with the database management system.

To view the PostgreSQL server version, use the command:

psql -V

To exit the PostgreSQL prompt, type:

\q

This will bring you back to the postgres Linux command prompt.

1 - Allow remote connections to the PostgreSQL server residing on Linux

To configure Postgres for external connections, follow these steps:

1. Open the PostgreSQL configuration file:

sudo nano /etc/postgresql/10/main/postgresql.conf

Then, install a repository configuration package using the official PostgreSQL repository for the desired Linux distro.

2. Find the following line:

#listen_addresses = 'localhost'

3. Uncomment it and change the value to '*', allowing connections from any IP address:

listen_addresses = '*'

4. Save and close the file.

5. Next, modify the pg_hba.conf file to also allow connections from everyone. Open the file:

sudo nano /etc/postgresql/10/main/pg_hba.conf

6. Modify the following section:

# IPv4 local connections: host all all 127.0.0.1/32 md5

to this:

# IPv4 local connections: host all all 0.0.0.0/0 md5

This file stores the client authentication, with each record specifying an IP address range, database name, username, and authentication method. By granting all database users access to all databases with any IP address range, you are allowing connections from any IP address.

7. Save and close the file.

8. Allow port 5432 through the firewall by executing:

sudo ufw allow 5432/tcp

9. Finally, restart PostgreSQL server to apply all the changes:

sudo systemctl restart postgresql

2 - Connect to a remote PostgreSQL server on Linux from dbForge Studio for PostgreSQL

1. On the Database menu, select New Connection.

2. On the Database Connection Properties> General tab, specify the connection details:

• Host: Provide the host name
• Port: Provide the port number
• User and Password: Enter respective user credentials. By default, the password is saved automatically. If you don’t want to save the password, clear the Allow saving password checkbox.
• Database: Enter the name of a PostgreSQL database you want to connect to or select it from the drop-down list.
• Optional: Connection Name: The connection name is generated automatically from the host name. If you want to create a distinctive name for the connection, type the new name.

Click Test Connection to verify the connection details you have provided. Click Connect to connect to a PostgreSQL server.

Uninstall PostgreSQL on Linux distros that use the Apt package management system

To uninstall PostgreSQL on Linux, you can follow the steps below:

1. Stop the PostgreSQL server: If the PostgreSQL server is running, you need to stop it before you can uninstall it. You can do this by using the following command:

sudo systemctl stop postgresql

2. Remove the PostgreSQL packages: The next step is to remove the PostgreSQL packages from your system. You can do this by using the following command:

sudo apt-get remove postgresql postgresql-contrib

3. Remove the data directory: By default, PostgreSQL stores its data in the /var/lib/postgresql/ directory. After you have removed the packages, you need to remove the data directory as well. You can do this by using the following command:

sudo rm -r /var/lib/postgresql/

4. Remove other related files: Finally, you should also remove any other related files, such as configuration files and log files that may still be on your system. You can do this by using the following command:

sudo rm -r /etc/postgresql/

Uninstall PostgreSQL on Linux distros that use the Yum package management system

To uninstall PostgreSQL from a Linux system that uses the Yum package manager, you can use the following command:

sudo yum remove postgresql

This will remove the PostgreSQL package and any related dependencies from your system. If you want to also remove the configuration files and data directory, you can use the following command:

sudo yum remove postgresql\*

Be sure to remove the pgsql directory as well:

sudo rm /var/lib/pgsql