reset postgres password and backup / restore databases

reset postgres password in case of being forgotten

change postgres authentication method from md5(or peer) to trust. It can be done in the configuration file under /etc/postgresql/10/main/pg_hba.conf

restart postgresql service to reload configuration file
login to postgresql by using

psql -U postgres # login to database
postgres=# alter user postgres with password 'NEW_PASSWORD'; #change password to NEW_PASSWORD
postgres=# \q

backup database

without login to database. Login to postgres user

sudo su - postgres

then run

gp_dump databasename > db.bak #backup one database
pg_dump -U username -W -F t database_name > c:\backup_file.tar
pg_dumpall > all_db.bak

user possible to login to database with username and password

set user authentication method to md5

The problem is still your pg_hba.conf file (/etc/postgresql/9.1/main/pg_hba.conf*).

This line:

local   all             postgres                                peer

Should be:

local   all             postgres                                md5