how to install PostgreSQL 12 on Ubuntu / Debian 10,11

4
(1)

Step 1: Update system

It is recommended to update your current system packages if it is a new server instance.

sudo apt update
sudo apt -y upgrade
sudo shutdown -r now

Step 2: Add PostgreSQL 12 repository

The GPG key used for signing packages needs to be imported to the system:

sudo apt update
sudo apt -y install gnupg2
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

After importing GPG key, add PostgreSQL repository:

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Step 3: Install PostgreSQL 12 on Debian 11 / Debian 10

Now the repository has been added successfully, update the package list and install PostgreSQL 12 on Debian 11 / Debian 10 Linux system.

sudo apt update
sudo apt -y install postgresql-12 postgresql-client-12

A successful installation prints a message. The PostgreSQL service is started and set to come up after every system reboot.

$ systemctl status postgresql.service 
 ● postgresql.service - PostgreSQL RDBMS
    Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
    Active: active (exited) since Sun 2021-10-06 10:23:46 UTC; 6min ago
  Main PID: 8159 (code=exited, status=0/SUCCESS)
     Tasks: 0 (limit: 2362)
    CGroup: /system.slice/postgresql.service
 Oct 06 10:23:46 deb10 systemd[1]: Starting PostgreSQL RDBMS…
 Oct 06 10:23:46 deb10 systemd[1]: Started PostgreSQL RDBMS.

$ systemctl status [email protected][email protected] - PostgreSQL Cluster 12-main
    Loaded: loaded (/lib/systemd/system/[email protected]; indirect; vendor preset: enabled)
    Active: active (running) since Sun 2021-10-06 10:23:49 UTC; 5min ago
  Main PID: 9242 (postgres)
     Tasks: 7 (limit: 2362)
    CGroup: /system.slice/system-postgresql.slice/[email protected]
            ├─9242 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
            ├─9254 postgres: 12/main: checkpointer   
            ├─9255 postgres: 12/main: background writer   
            ├─9256 postgres: 12/main: walwriter   
            ├─9257 postgres: 12/main: autovacuum launcher   
            ├─9258 postgres: 12/main: stats collector   
            └─9259 postgres: 12/main: logical replication launcher   
 Oct 06 10:23:47 deb10 systemd[1]: Starting PostgreSQL Cluster 12-main…
 Oct 06 10:23:49 deb10 systemd[1]: Started PostgreSQL Cluster 12-main.

$ systemctl is-enabled postgresql
enabled

Step 4: Test PostgreSQL Connection

During installation, a postgres user is created automatically. This user has full superadmin access to your entire PostgreSQL instance. Before you switch to this account, your logged in system user should have sudo privileges.

sudo su - postgres

Let’s reset this user password to a strong Password we can remember.

psql -c "alter user postgres with password '[email protected]'"

Start PostgreSQL prompt by using the command:

$ psql

Get connection details like below

[email protected]:~$ psql
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

List databases:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Step 5: Configure remote Connection

Installation of PostgreSQL only accepts connections from localhost. In ideal production environments, you’ll have a central database server and remote clients connecting to it – But of course within a private network (LAN).

To enable remote connections, edit PostgreSQL configuration file:

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

Uncomment line 59 and change the Listen address to accept connections within your networks.

# Listen on all interfaces
listen_addresses = '*'

# Listen on specified private IP address
listen_addresses = '192.168.1.11'

After the change, restart postgresql service.

sudo systemctl restart postgresql

Confirm Listening addresses.

# netstat  -tunelp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      111        112837     11143/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      111        112838     11143/postgres      

And finally allow remote connections for users in your network.

sudo nano /etc/postgresql/12/main/pg_hba.conf
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
host    all             all             0.0.0.0/0               trust

That’s all, folks

Similar Posts:

131

How useful was this post?

Click on a star to rate it!

Average rating 4 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

Scroll to Top