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)

SQL Shell

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.

• user1822: What is this "PostgreSQL shell" and how do you start it? Is that a Windows program, or do you run that inside the WSL container?
• Shruti: When you install PostgreSQL on Windows, it comes with an SQL Shell (psql) that runs on Windows.
• user1822: So how do you start your psql in windows? Please show us the complete command line you are using. Does it connect to the Windows instance or the Linux (=WSL) instance?
• Shruti: It is independent of Linux. It's a part of Windows' PostgreSQL setup. I've been using it to access my DB for all other purposes. I've added a snapshot of the same in the question above.
• user1822: So with one tool to connect to you Postgres database on Windows and with the other you connect to your Postgres database on Linux - two completely different installations. Why are you surprised they have different databases?

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"