0

EDIT: the original question can be found below. Basically, this is a misconception on my part of how pg_ctl works and if you're getting this message too (from pg_ctl), then the TLDR; is, as @jjanes explains in the accepted answer:

pg_ctl doesn't track your database. It interacts with a specific directory, either specified by -D, or through the PGDATA env variable.

So the postgres you're connected to must have been started somehow, maybe not by you, but was given a PGDATA directory to work with.

pg_ctl is not your interface to multiple running postgres instances.


(original question):

I created a directory in postgres user's home dir called mydb: /var/lib/postgresql/mydb

I did this using initdb -D mydb.

Now, I want to start postgres and use mydb as the location where all this db's data is stored; so I run:

pg_ctl -D /var/lib/postgresql/mydb start

It starts successfully, but if I try:

psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# show data_directory;
       data_directory        
-----------------------------
 /var/lib/postgresql/12/main
(1 row)

i.e. it's using the "main" or default data directory, completely ignoring the -D argument.

So now it occurs to me that I might be connecting to a different db... even though running pg_ctl status before pg_ctl start gives me:

pg_ctl: no server running

However, this has to be the case because... with "no server running", I am able to psql and query the main database.

So then, how do I get the main database "tracked" by pg_ctl? Also, how do I use psql to connect to the right database?

Thank you

Edit: was able to connect to the postgres started with pg_ctl using:

psql -h /var/run/postgresql

(even though I don't see any changes to that directory's contents before / after I pg_ctl start i.e. it remains: 12-main.pg_stat_tmp 12-main.pid).

Edit: Also I can see that the postgres process not being tracked by pg_ctl was started using postgres executable (from running pstree -a):

postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

Probably from a startup file. I would imagine I need to find this startup file and replace use of postgres with pg_ctl...

1

pg_ctl doesn't track your database. It interacts with a specific directory, either specified by -D, or through the PGDATA env variable. If neither are set, then it throws an error.

$ unset PGDATA
$ pg_ctl status
pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

If your database truly did successfully start up, then it either must be configured to run on a non-default port, or it must be configured not to use a port at all but only a unix domain socket. Neither of these configurations will be set up for you automatically by initdb, so I think you may be mistake that it started up in the first place. Or maybe you did something beyond what you have described to us.

The Unix domain socket is named with a leading ., which makes it a hidden file. You will only see it in /var/run/postgresql with ls if you give the -a option to it.

What pg_ctl start does behind the scenes is call postgres, so the database is always started via postgres. It might be directly, or indirectly, or even more indirectly, but eventually that is how it is started.

It is not clear what it is you want to do. If you want to run out of your own directory, then you probably want to disable the start up script that runs the other one automatically. On the other hand, why not just use the one that is already configured and managed for you by the packaging system? You can have both running side by side, buy why?

1
  • Thanks jjanes! I agree that the question is not clear. Mainly, what was bugging me was pg_ctl telling me "no server running" when I was connected to postgres and issuing commands. I see now that I had the PGDATA env variable set to ~/mydb and that I was assuming pg_ctl was my interface to multiple postgres instances. Re "why have multiple instances"? I'm just doing it to learn really. EDIT: I'll try to make the question title clearer. – justin May 1 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.