psql on WSL: Not listing all databases for owner postgres

Question

I'm trying to setup Postgres on WSL (bash) to be able to use pg_dump (as an absolute beginner).

I've followed the steps from here twice, once after purging the entire installation.

At step 3: I used

sudo service postgresql status

as

systemctl status postgresql.service

returned

"System has not been booted with systemd as init system (PID 1). Can't operate.
Failed to connect to bus: Host is down"

After starting the service using

sudo service postgresql start

I accessed postgres using

sudo -u postgres psql

Here, the list of databases returned are

List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)

While PostgreSQL shell returns me the list of ALL my databases.

List of databases
   Name    |  Owner   | Encoding |          Collate           |           Ctype            |   Access privileges
-----------+----------+----------+----------------------------+----------------------------+-----------------------
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 snappdev  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |          |          |                            |                            | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |          |          |                            |                            | postgres=CTc/postgres
(4 rows)

snappdev database is what I'm trying to take a dump of. However, it is not listing on WSL (ubuntu). I also tried changing permissions (chmod / chown). I have made the recommended changes on postgresql.conf and pg_hba.conf.

Also, after I do

sudo -u postgres

I'm constantly being asked for postgres password even when I'm trying to change its password. For this, I've also tried

psql -c "alter user postgres with password 'StrongAdminP@ssw0rd'"

which keeps asking me the password for the user, and

sudo passwd postgres

along with other steps recommended here.

I'm not really sure at this point what I'm missing or if taken some terribly wrong steps. Appreciate any help on this.

Answer 1

If you (like me) have PostgreSQL installed on Windows and are trying to take a backup, there's pg_dump.exe utility (which I had not come across before). The steps to take a backup/dump are:

1. Open command prompt.
2. cd ../..
3. cd C:\Program Files\PostgreSQL\<version>\bin
4. pg_dump -h localhost -p 5432 -U postgres dbname > "path/dumpfile.sql"