PostgreSQL (aka Postgres), is an open-source SQL relational database management system (RDBMS). These RDBMS are the key component for the majority of all the web sites and web applications, providing an efficient way to persist, organize, and access information.
In the blog, I’ll demonstrate, how to install Postgres 9.6 for Ubuntu 14.04, 16.04 LTS.
Step 1: Add Postgresql repository
$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
$ wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Output:
Step 2: Install Postgresql 9.6
$ sudo apt-get update
$ sudo apt-get install postgresql-9.6 postgresql-contrib
$ sudo apt-get install postgresql-9.6 postgresql-contrib
The apt-get update, updates the list of available packages and their versions, above we have added the postgresql repository, in the output you can see the apt-postgresql gets added.
Output:
Verification – Using psql shell (Optional)
Postgresql comes with an interactive Postgres prompt. On installation the super user postgres gets created. A good practice is to update the password for postgres user and create a separate user for your service or web app.
$ sudo -u postgres psql postgres
Output:
Show all the 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)postgres=#
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)postgres=#
Show users/roles
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}postgres=#
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}postgres=#
Connection Info
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
Create a New Database
postgres=# create database testing_db;
Create a new Role
postgres=# create user nahmed with password 'password';
postgres=# grant all privileges on database testing_db to nahmed;
postgres=# grant all privileges on database testing_db to nahmed;
Change password
postgres=# ALTER USER postgres WITH PASSWORD 'yournewpassword';