Tag: Install PostgreSQL 14

Install PostgreSQL 14

1. Update Ubuntu and get PostgreSQL requirements

sudo apt update
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null

sudo apt update

2. Get PostgreSQL 14 installed on your Ubuntu system

sudo apt install postgresql-14

3. PostgreSQL’s default port (5432) should not be held hostage by any other system process.

sudo ss -atnp | grep 5432

4. Next, restart, enable PostgreSQL so that it keeps running even after your Ubuntu system reboots, and check on the status of PostgreSQL to make sure it is running.

sudo systemctl restart postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql 

5. Connecting to PostgreSQL 14 Database in Ubuntu

sudo -u postgres psql

6. For example, to create a PostgreSQL admin superuser that has all the database user privileges, we would execute the following query.

CREATE ROLE <username> WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD  '<password>';

<username> : any usernames , <password> : any passwords

7. To confirm the existence of the PostgreSQL superuser, execute the following command.

\du

8. To exit PostgreSQL shell, run the command

exit;

---

Remote Configuration PostgreSQL 14

1. Edit the configuration file to allow remote access

vi /etc/postgresql/14/main/postgresql.conf

Look for this line in the file:

listen_addresses = 'localhost'

Uncomment, and change the value to '*', this will allow Postgres connections from anyone.

listen_addresses = '*'

2. Save and exit the file. Next, modify pg_hba.conf to also allow connections from everyone. Open the file with your preferred editor:

vi /etc/postgresql/14/main/pg_hba.conf

Modify this section:

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

To this:

# IPv4 local connections:
host    all             all             0.0.0.0/0            scram-sha-256

3. Allow PostgreSQL port from Firewall

sudo ufw allow 5432/tcp

4. Restart PostgreSQL

sudo systemctl restart postgresql