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:

\conninfo
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 127.0.0.1/32 md5

More: http://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge