How to manage postgres through command line in Ubuntu?

How to manage postgres through command line in Ubuntu?

25 Apr 2018
0 Comments
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.

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 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

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

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

 

This is all about postgres. Hope this is helpful.

Thank You.




Leave a comment: