unable to run postgres with pg_ctl

Question

I was reading a postgresql book (PostgreSQL from Novice to Pro) and it says you can start the postmaster daemon by invoking the postgres binary which was part of the installation:

/usr/lib/postgresql/9.3/bin/postgres "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"

Notice we had to pass both the data directory and configuration as parameters to the postgres binary.

The book then goes on to say that the pg_ctl executable, which also happens to be stored in the /usr/lib/postgresql/9.3/bin directory when installing via apt-get, is used to simplify the task. But I cannot get pg_ctl to run:

$ /usr/lib/postgresql/9.3/bin/pg_ctl -D "/var/lib/postgresql/9.3/main" start
server starting
postgres@estonia:/usr/lib/postgresql/9.3/bin$ postgres cannot access the server configuration file "/var/lib/postgresql/9.3/main/postgresql.conf": No such file or directory

It says it cannot find the configuration file, but there is no way to specify the configuration file here:

$ /usr/lib/postgresql/9.3/bin/pg_ctl start "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"
pg_ctl: too many command-line arguments (first is "start")
Try "pg_ctl --help" for more information.

How do I get pg_ctl to run?

Answer 1

I use the source version and have never had any problems - here is the short version of the install for the source distribution.

 Short Version
./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

initdb initialises the server instance after install (an install from an rpm or apt-get may take care of this for you).

postgres -D /usr/local/pgsql/data > logfile 2>&1 &

or

./bin/pg_ctl -D ./data/ -l logfile start

Should start the server for you - I generally use the pg_ctl option. (When you run initdb, you should see the above commands as a hint as to how to start the server - but, again, you may not have to run initdb).

Then you can use createdb test to create your first database.

If I were you, I'd start by executing

$export PATH=/usr/lib/postgresql/9.3/bin:$PATH 

(or even better, put it in your .bashrc).

Then try running

pg_ctl -D /var/lib/postgresql/9.3/main/ -l logfile start

and then

createdb my_test - it will be in the ...9.3/main directory.

Notice, there are no quotes around -D or the data path.

Running pg_ctl --help

gives

-c, --core-files allow postgres to produce core files

I think that PostgreSQL should pick up your postgresql.conf without the need to use -c - I'm not even sure that it's a correct option.

Then run psql test and you should be connected to your test database.

• Donato: I installed postgresql from apt-get. Do you think that's why it cannot find the configuration file? The configuration file is for sure under /etc/postgresql/9.3/main when using apt-get. But pg_ctl cannot find it. You think I should purge it and install from source and these problems go away?
• Vérace: Have you set the PGDATA environmental variable? There are a few of these - Google these - but they're not necessary if you specify paths yourself. From source, I've never had an issue with this. Specifying the data directory in your command line should sort that out - since postgresql.conf is in the datadir. You can install from source without purging - just make sure that your apt-get server isn't working - or at least that the port is different. I'm surprised that apt-get doesn't do all of this for you - I use a RedHat based distro - but I always install from source, so YMMV!
• Vérace: I was just looking at the options with postgres as opposed to pg_ctl. It appears that postgres offers a lot more choice of options (e.g. port number) - just run postgres --help to check them out.

Answer 2

When used to start the instance, pg_ctl accepts options to the postgres executable, passed with -o

From its manpage:

 -o options
 Specifies options to be passed directly to the postgres command.
 The options should usually be surrounded by single or double quotes
 to ensure that they are passed through as a group.

So your last command in the question should be amended as:

/usr/lib/postgresql/9.3/bin/pg_ctl start \
 "-D" "/var/lib/postgresql/9.3/main" \
 "-o -c config_file=/etc/postgresql/9.3/main/postgresql.conf"

• alfonx: > "-o -c config_file=/etc/postgresql/9.3/main/postgresql.conf"Very unintuitive quotes.. Thanks for showing me