How to manage postgres through command line in Ubuntu?

How to manage postgres through command line in Ubuntu?

25 Apr 2018
How to manage postgres through command line in Ubuntu?

Step 1: First install Postgres in the Ubuntu system using the following commands.

$  sudo apt-get update
$  sudo apt-get install postgresql postgresql-contrib libpq-dev

To check psql (postgres) version.

$  psql –version


Step 2:  Now to create root user and password for psql.

$  sudo -u postgres createuser -s root
$  sudo -i -u  postgres

Now, you are in postgres environment.


Now, use the following command to enter and manage psql.

postgres@admin:~$  psql

Now, set the password for psql username “root”.

postgres=# \password root
enter password

Now, you can exit from psql using following command

postgres=# \q


Step 3:  Create a new user and database in psql

$ sudo su postgres
$ psql -c "create user mack with password 'mack'"
$ psql -c "create database mackdb owner mack"
$ sudo -i -u postgres
postgres@admin:~$  psql


Step 4:  Give all privileges over the database to a particular user.

postgres=# grant all privileges on database mackdb to mack

You might be facing this type of error while rakedb:create

PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations rakedb:create


To list all the users of psql.

postgres=# \du

To list all the databases of psql.

postgres=# \l


Step 5:  Take backup or dump file to the database.

$ sudo su postgres

To take the backup of psql database

postgres@admin:~$ pg_dump dbname > outfile

To dump into psql database. Go to a particular directory where the dump file is present enter the following command

postgres@admin:~$  pg_restore dbname < infile


This is all about postgres. Hope this is helpful.

Thank You.

Leave a comment: