How to Install PostgreSQL on Ubuntu 18.04
Install PostgreSQL on Ubuntu
Install PostgreSQL on Ubuntu by using following command.
sudo apt update
sudo apt install postgresql postgresql-contrib
Output:
root@vps:~# sudo apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libpq5 libsensors4 postgresql-10 postgresql-client-10
postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
lm-sensors postgresql-doc locales-all postgresql-doc-10 libjson-perl
openssl-blacklist isag
The following NEW packages will be installed:
Verifying PostgreSQL Installation
sudo -u postgres psql -c "SELECT version();"
Output:
root@vps:~# sudo -u postgres psql -c "SELECT version();"
could not change directory to "/root": Permission denied
version
----------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)
To log in to the PostgreSQL server as the postgres user first you need to switch to the user postgres
and then you can access a PostgreSQL prompt using the psql utility
sudo su - postgres
psql
Output:
postgres@vps:~$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
To exit out of the PostgreSQL shell type.
\q
You can also access the PostgreSQL prompt without switching users using the sudo command.
sudo -u postgres psql
The postgres user is typically used only from the local host and it is recommended not to set the password for this user.
Creating PostgreSQL Role and Database
Create a new PostgreSQL Role.
sudo su - postgres -c "createuser john"
Create a new PostgreSQL Database.
sudo su - postgres -c "createdb johndb"
Grant privileges.
sudo -u postgres psql
grant all privileges on database johndb to john;
Output:
postgres=# grant all privileges on database johndb to john;
GRANT
To exit out of the PostgreSQL shell type.
\q
Enable remote access to PostgreSQL server.
By default, the PostgreSQL server listens only on the local interface 127.0.0.1. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section.
sudo vim /etc/postgresql/10/main/postgresql.conf
Output:
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
Restart the PostgreSQL service.
sudo service postgresql restart
Verify the changes with the ss utility.
ss -nlt | grep 5432
Output:
root@vps:~# ss -nlt | grep 5432
LISTEN 0 128 127.0.0.1:5432 0.0.0.0:*
LISTEN 0 128 [::1]:5432 [::]:*