Linux Tutorials, Guides & Latest Tech Stuffs

How to Install PostgreSQL on Ubuntu 16.04

PostgreSQL is a powerful open source relational database system, it provides a way for implementation of structural query language SQL. PostgreSQL can handle load from small single machine to  large internet facing application with many concurrent users. It comes with feature like reliable transactions and concurrency without read locks.

Install PostgreSQL Ubuntu 16.04

Ubuntu default repositories contains PostgreSQL packages. We can install postgresql and postgresql-contrib for some additional functionality using using apt package manager

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

After running these commands you may find similar output on your Ubuntu terminal

 The following NEW packages will be installed:
 libpq5 libsensors4 postgresql postgresql-9.5 postgresql-client-9.5 postgresql-client-common postgresql-common postgresql-contrib
 postgresql-contrib-9.5 ssl-cert sysstat
 0 upgraded, 11 newly installed, 0 to remove and 0 not upgraded.
 Need to get 4,881 kB of archives.
 After this operation, 19.7 MB of additional disk space will be used.
 Do you want to continue? [Y/n]

Just type in y from keyboard and hit enter button your postgresql installation will be started. After successful installation you would require to perform basic database operations like create database, create user, create password for user etc. In the paragraphs below you can find guides for this

Using PostgreSQL

Switching over to postgres account

After completion of installation, a default user postgres  is created with the default Postgres role is automatically completed. We will use this user to login and use postgres

Switch over to the postgres user account by typing following command at terminal

sudo -i -u postgres

after running the above command you will find change on terminal and user should be switched  as

[email protected]:~$

Now run

psql

your terminal user will be switched again and now you should see

postgres-#

user on your terminal

List postgres databases

After reaching at postgres-# simply type forward slash ( \) and l together or \list to list all postgres databases

\list

Create new postgres database

you can run following command to create a database named “test”, don’t forget to use semicolon at end

Create database test;

Connect to a postgres database

you can run following command to connect your required database on psql sql

\connect <database>

List all postgres databases

you can run \list command to list all postgresql database

\list

Create postgres User

For creating username you have to just type in create user with your username, please change <username> with your required username

create user <username>;

or

create role <rolename/username>

Create or change password for postgres user

alter user <username> password 'required-password';

You can also use following command to change password interactively for postgres user

\password <username>

You will prompt with Enter new password just type in your new password and Enter it again, password will be changed successfully

List all postgres users

You can run forward slash (\) du to list all users like \du

\du

Delete postgres user

For deleting a user on which no object depends, you can use

drop user <username>;

or you can use

drop role  <username/rolename>

List all tables in postgres database

first connect to that postgres database, using

\connect <database-name>

or

\c <database-name>

and then you can list all tables for the database using \dt command

\dt

Conclusion

you would have a quite a good idea about PostgreSQL installation on Ubuntu 16.04 VPS. You can also  install pgAdmin4 on Ubuntu 16.04 which is a popular tool for postgres database administration

Pranav K

Pranav K is a software engineer and all-round computer geek. His interests include AWS, Ubuntu and Wordpress

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.