Introduction
Installing PostgreSQL 14 on an Ubuntu server is a simple process that can be completed in a few steps. While the Ubuntu repositories may not always have the latest version, it is recommended to use the official PostgreSQL repository for the most up-to-date installation. This step-by-step guide will walk you through the process.
Step 1: Update the System
Before proceeding with the installation, it is important to ensure that your system is up to date. Open the terminal and run the following command:
|
1 2 3 |
sudo apt update && sudo apt upgrade |
Step 2: Add the PostgreSQL Repository
Next, you will need to add the official PostgreSQL repository to your system. This can be done by running the following commands:
|
1 2 3 |
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' |
|
1 2 3 |
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - |
|
1 2 3 |
sudo apt update |
Step 3: Install PostgreSQL 14
Once the repository is added, you can proceed with the installation of PostgreSQL 14. Run the following command to install:
|
1 2 3 |
sudo apt install postgresql-14 |
During the installation, you will be prompted to set a password for the PostgreSQL superuser (postgres). Choose a strong password and remember it for future use.
Step 4: Verify the Installation
After the installation is complete, you can verify that PostgreSQL 14 is running correctly. Run the following command to check the status:
|
1 2 3 |
sudo systemctl status postgresql.service |
If PostgreSQL is running, you should see a message indicating that the service is active and running.
Login to PostgreSQL: By default, PostgreSQL creates a user named postgres. Switch to this user and connect to the PostgreSQL shell:
|
1 2 3 |
sudo -u postgres psql |
This will take you to the PostgreSQL command prompt.
Step 5: Configure PostgreSQL
By default, PostgreSQL is configured to only allow connections from the local machine. If you want to allow remote connections, you will need to make some changes to the configuration file.
To edit the configuration file, run the following command:
Access Cofiguration:
Access from All IP:
|
1 2 3 |
sudo nano /etc/postgresql/14/main/postgresql.conf |
Find the line that starts with “listen_addresses” and uncomment it by removing the “#” at the beginning. Replace “localhost” with “*” to allow connections from any IP address.
Access from Specific IP address:
|
1 2 3 |
sudo nano /etc/postgresql/14/main/pg_hba.conf |
Add the Following Line
|
1 2 3 |
host all all 192.168.0.0/24 |
Change the postgres User Password: In the PostgreSQL shell:
|
1 2 3 |
\password postgres |
Create a New Database and User (Optional):
Still in the PostgreSQL shell, you can create a new database and a new user if needed:
|
1 2 3 4 5 |
CREATE DATABASE classicdba; CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword'; GRANT ALL PRIVILEGES ON DATABASE classicdba TO myuser; |
Exit PostgreSQL Shell:
|
1 2 3 |
\q |
Save the file and exit the text editor.
Step 6: Restart PostgreSQL
After making changes to the configuration file, you will need to restart PostgreSQL for the changes to take effect. Run the following command to restart the service:
|
1 2 3 |
sudo systemctl restart postgresql |
Step 7: Test the Remote Connection
To test the remote connection, you can use a PostgreSQL client such as psql or pgAdmin. Install psql by running the following command:
|
1 2 3 |
sudo apt install postgresql-client |
Once installed, you can connect to the remote PostgreSQL server using the following command:
|
1 2 3 |
psql -h <server_ip> -U postgres -d <database_name> |
Replace <server_ip> with the IP address of your Ubuntu server.
Step 8 : Secure Your Installation
Ensure that your PostgreSQL installation is secure, especially if exposed to the internet:
- Firewall Configuration: Restrict access to the PostgreSQL port (default 5432) to trusted IPs only.
- Regular Updates: Keep your PostgreSQL installation and Ubuntu server regularly updated for security patches.
Conclusion
Congratulations! You have successfully installed PostgreSQL 14 on your Ubuntu server. You can now start using PostgreSQL for your database needs. Remember to follow best practices for securing your PostgreSQL installation and regularly update your system and PostgreSQL version for optimal performance and security.
