How To Install, Back Up and Restore PostgreSQL

1. Installation PostgreSQL:

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

2. Change user’s password in PostgreSQL:

sudo -u postgres psql template1 [template1 is a database]
ALTER USER postgres with encrypted password ‘your_password’;

=> Message: ALTER ROLE

3. Connect to PostgreSQL:

sudo -u postgres psql


🔹 You can view information about the Postgres user and the database you're currently connected by:

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".


🔹 You can list databases by:

postgres=# \l


🔹 You can list roles by:

postgres=# \du


🔹 You can exit out of Postgres by:

postgres=# \q

4. Dump PostgreSQL:

pg_dump -Fc --verbose --clean --no-acl --no-owner -h localhost -U postgres -d dbname > db_dump.dump


5. Restore PostgreSQL:

Go to folder contain db <db_dump.dump>

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d dbname db_dump.dump


6. Drop PostgreSQL:

dropdb -U postgres -h localhost db_name

7. Error connection: psql -U postgres -h localhost

FATAL: Peer authentication failed for user “postgres”

Change ‘peer’=>‘md5’ or ‘trust’ (should md5) in /etc/postgresql/9.1/main/pg_hba.conf

# Database administrative login by Unix domain socket
local all postgres md5

# IPv4 local connections:
host all all md5