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 'SuperStrongAdminP@ssw0rd'"
Start PostgreSQL prompt by using the command:
$ psql
Get connection details like below
postgres@pgsrv01:~$ 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:
- how to upgrade postgresql 11 to 12 using pg_upgradecluster
- postgresql: how to reset forgotten postgres password
- postresql: move data directory to another location
- High reliability PostgreSQL cluster based on Patroni, Haproxy, Keepalived
- How to create Dblink between Oracle & PostgreSQL