PostgresSQL 11 Installation Prerequisites
Before initiating the installation process of PostgresSQL 11 we need to complete following prerequisites steps.
sources.list entry for PostgresSQL by creating
/etc/apt/sources.list.d/pgdg.list file. Add following contents to the newly created file by replacing UBUNTU_VERSION with the codename of installed Ubuntu release.
deb http://apt.postgresql.org/pub/repos/apt/ UBUNTU_VERSION-pgdg main
UBUNTU_VERSION or LSB (Linux Standard Base) mentioned above can be retrieved using using
lsb_release command as following.
'c'is for codename.
's'is for short output.
echo commands, we can automate the file creation process as following.
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
Run following command to verify contents of repository file.
Next we need to import the repository signing key and update the apt package list as following.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 4812 100 4812 0 0 2868 0 0:00:01 0:00:01 --:--:-- 2867 OK
apt-get update command to update package list.
sudo apt-get update
Install PostgreSQL 11 Server
Install PostgresSQL 11 using apt package manager.
$ sudo apt -y install postgresql-11
Allow access to PostgreSQL from remote hosts
By default access to PostgresSQL server is allowed only from local host. Here we will make the required configurations to allow remote access to newly installed PostgresSQL server.
Run Socket Statistics (ss) command to show IP binding against port 5432 (default PostgresSQL tcp port).
sudo ss -tunelp | grep 5432
Open the PostgresSQL configuration file in editor to make required changes for remote access.
sudo nano /etc/postgresql/11/main/postgresql.conf
Look for the
listen_addresses configuration entry under
CONNECTIONS AND AUTHENTICATION section,
listen_addresses property specifies TCP/IP address(es) where PostgresSQL server will listen for incoming client connections. Set
'*' as the value of
listen_addresses property to bind PostgresSQL server with all available IPs on all network interfaces of this system.
listen_addresses = '*'
Alternatively, you can specify a particular IP Address as following.
listen_addresses = '192.168.17.12'
To allow remote connection to PostgresSQL server
PostgresSQL server uses
pg_hba.conf (Postgres Host Based Authentication) configuration file to manage client authentication, to allow remote PostgresSQL client to connect and authenticate we need to add entry for that that client in
pg_hba.conffile for editing as following.
sudo nano /etc/postgresql/11/main/pg_hba.conf
- Add entry for a specific client IP or range as following.
host all all 192.168.0.75/32 md5
Or you may add the the complete range 192.168.0.X as following.
host all all 192.168.0.0/32 md5
Restart PostgresSQL service to load the updated configuration.
sudo systemctl restart postgresql
Run following command to confirm the bind address for PostgresSQL server.
$ sudo ss -tunelp | grep 5432 tcp LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=16066,fd=3)) uid:111 ino:42972 sk:8 <-> tcp LISTEN 0 128 [::]:5432 [::]:* users:(("postgres",pid=16066,fd=6)) uid:111 ino:42973 sk:9 v6only:1 <->
Verify the successful start of PostgresSQL service by executing following command.
sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Wed 2019-04-03 01:31:42 PKT; 39min ago Process: 1763 ExecReload=/bin/true (code=exited, status=0/SUCCESS) Process: 6940 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 6940 (code=exited, status=0/SUCCESS) Apr 03 01:31:42 u-srv-1 systemd: Starting PostgreSQL RDBMS... Apr 03 01:31:42 u-srv-1 systemd: Started PostgreSQL RDBMS.
Updating Password of the default admin of PostgresSQL Server
The default admin user for PostgresSQL server is named as postgres, execute following commands to set password for postgres user.
- Switch the current user to postgres using
sudo su - postgres
- Update the password for postgres user by executing
alter user postgres with passwordSQL statement with
psqlPostgresSQL client as following.
postgres@os1:~$ psql -c "alter user postgres with password 'StrongPassword'"
- All done, now run
logoutcommand to return back to your own logged-in user.
This was a step by step guide to install PostgresSQL server on Ubuntu linux machine. You like this article, have any questions or suggestions please let us know in the comments section.
Thanks and Happy Learning.