How to manage postgres through command line in Ubuntu?

Step 1: First install postgres in Ubuntu system using 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.

postgres@admin:~$

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
confirm

Now, you can exit from psql using following command

postgres=# \q

Step 3:  Create 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 database to a particular user.

postgres=# grant all privileges on database mackdb to mack

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

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

postgres=# ALTER USER mack WITH SUPERUSER;

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 particular directory where the dump file is present enter following command

postgres@admin:~$  pg_restore dbname < infile
exit.

This is all about postgres. Hope this is helpfull.

Thank You.

Source: Manage Postgre in Ubuntu