Can't restart Postgres

On 02/14/2017 11:17 AM, Shawn Thomas wrote:

I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running Postgres instance (9.4) which caused it to shut down. The last line of main.log:

FATAL: could not load server certificate file "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

I've since restored the cert but cannot get Postgres to start back up. It's the Debian 8 packaged version which complicates the debugging and troubleshooting. There doesn't seem to be a way to do anything with Postgres outsided the of Debian's systemd wrappers. All I've got to work with is from /var/syslog:

pangaea systemd[1]: Starting PostgreSQL Cluster 9.4-main...
pangaea postgresql@9.4-main[28684]: Error: could not exec start -D /var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s -o -c config_file="/etc/postgresql/9.4/main/postgresql.conf" :
pangaea systemd[1]: postgresql@9.4-main.service: control process exited, code=exited status=1

Any help/advice would be greatly appreciated.

It is likely a permissions issue. What does the systemctl log say?

JD

-Shawn

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 28668 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/postgresql.service

-Shawn

On 02/14/2017 11:43 AM, Shawn Thomas wrote:

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 28668 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/postgresql.service

What about if use pg_ctl as the postgres user? That will give you a
better idea.

jD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:

On 02/14/2017 11:43 AM, Shawn Thomas wrote:

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
Active: active (exited) since Tue 2017-02-14 10:48:18 PST; 50min ago
Process: 28668 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 28668 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/postgresql.service

What about if use pg_ctl as the postgres user? That will give you a better
idea.

You don't want ot be doing that on a systemd system, but try a combination
of pg_lsclusters and pg_ctlcluster. Might be you need to shut it down once
that way before it realizes it's down,and then start it back up.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Il 14/02/2017 20:31, Joshua D. Drake ha scritto:

On 02/14/2017 11:17 AM, Shawn Thomas wrote:

I inadvertently deleted the ssl-cert-snakeoil.pem out from under a
running Postgres instance (9.4) which caused it to shut down. The
last line of main.log:

FATAL: could not load server certificate file
"/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

I've since restored the cert but cannot get Postgres to start back
up. It's the Debian 8 packaged version which complicates the
debugging and troubleshooting. There doesn't seem to be a way to do
anything with Postgres outsided the of Debian's systemd wrappers.
All I've got to work with is from /var/syslog:

pangaea systemd[1]: Starting PostgreSQL Cluster 9.4-main...
pangaea postgresql@9.4-main[28684]: Error: could not exec start -D
/var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf" :
pangaea systemd[1]: postgresql@9.4-main.service: control process
exited, code=exited status=1

Any help/advice would be greatly appreciated.

It is likely a permissions issue. What does the systemctl log say?

I'd take a look with a simple ls -las in the certificate directory
(/etc/ssl/certs)... Not being sure but your postgres user should be at
least capable of reading it (the certificate), if not being the owner at
all....

Cheers
Moreno

JD

-Shawn

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes that would be the standard approach. But the Debian package removes pg_ctl from it normal place and wraps it with a perl script in a way that makes it difficult to work with (it doesn’t accept the same arguments):

https://wiki.debian.org/PostgreSql#pg_ctl_replacement <https://wiki.debian.org/PostgreSql#pg_ctl_replacement&gt;

@Mangnus, can you give me an example of how I might use pg_lsclusters and pg_ctlcluster? I’ve tried:

pg_ctlcluster 9.4 main start
Error: could not exec start -D /var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s -o -c config_file="/etc/postgresql/9.4/main/postgresql.conf”

-Shawn

Shawn Thomas <thomassd@u.washington.edu> writes:

I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running Postgres instance (9.4) which caused it to shut down. The last line of main.log:
FATAL: could not load server certificate file "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

There's a bit more to it than that, because simply deleting that file would
not result in a live server shutting down; before v10, the server only
examines its certificate file at startup.

I've since restored the cert but cannot get Postgres to start back up.

FWIW, I don't see how removing that file would result in a silent exit
without any error messages. I suspect you did more damage to the PG
installation than you've realized. As JD mentioned, permissions problems
on the executable are a possibility. I'm also wondering if you broke
the logging configuration, such that PG tries to write an error log
message but can't, or it's writing it somewhere other than where you
expect.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/14/2017 12:00 PM, Shawn Thomas wrote:

Yes that would be the standard approach. But the Debian package removes
pg_ctl from it normal place and wraps it with a perl script in a way
that makes it difficult to work with (it doesn’t accept the same arguments):

https://wiki.debian.org/PostgreSql#pg_ctl_replacement

@Mangnus, can you give me an example of how I might use pg_lsclusters
and pg_ctlcluster? I’ve tried:

I do not see a sudo below or is it apparent whether you are doing this
as the postgres user.

pg_ctlcluster 9.4 main start
Error: could not exec start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”

Not sure how close Debian 8 is to Ubuntu 16.04(something I use), but
from your first post they look like they share the same startup scripts.
So something like:

sudo systemctl restart postgresql@9.4-main.service

-Shawn

On Feb 14, 2017, at 11:52 AM, Magnus Hagander <magnus@hagander.net
<mailto:magnus@hagander.net>> wrote:

On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake <jd@commandprompt.com
<mailto:jd@commandprompt.com>> wrote:

On 02/14/2017 11:43 AM, Shawn Thomas wrote:

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service;
enabled)
Active: active (exited) since Tue 2017-02-14 10:48:18 PST;
50min ago
Process: 28668 ExecStart=/bin/true (code=exited,
status=0/SUCCESS)
Main PID: 28668 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/postgresql.service

What about if use pg_ctl as the postgres user? That will give you
a better idea.

You don't want ot be doing that on a systemd system, but try a
combination of pg_lsclusters and pg_ctlcluster. Might be you need to
shut it down once that way before it realizes it's down,and then start
it back up.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/14/2017 05:00 PM, Adrian Klaver wrote:

On 02/14/2017 12:00 PM, Shawn Thomas wrote:

Yes that would be the standard approach. But the Debian package removes
pg_ctl from it normal place and wraps it with a perl script in a way
that makes it difficult to work with (it doesn’t accept the same
arguments):

https://wiki.debian.org/PostgreSql#pg_ctl_replacement

@Mangnus, can you give me an example of how I might use pg_lsclusters
and pg_ctlcluster? I’ve tried:

I do not see a sudo below or is it apparent whether you are doing this
as the postgres user.

pg_ctlcluster 9.4 main start
Error: could not exec start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”

Not sure how close Debian 8 is to Ubuntu 16.04(something I use), but
from your first post they look like they share the same startup scripts.
So something like:

sudo systemctl restart postgresql@9.4-main.service

^^^^^^^
Should be start

-Shawn

On Feb 14, 2017, at 11:52 AM, Magnus Hagander <magnus@hagander.net
<mailto:magnus@hagander.net>> wrote:

On Tue, Feb 14, 2017 at 8:47 PM, Joshua D. Drake <jd@commandprompt.com
<mailto:jd@commandprompt.com>> wrote:

On 02/14/2017 11:43 AM, Shawn Thomas wrote:

pangaea:/var/log# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service;
enabled)
Active: active (exited) since Tue 2017-02-14 10:48:18 PST;
50min ago
Process: 28668 ExecStart=/bin/true (code=exited,
status=0/SUCCESS)
Main PID: 28668 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/postgresql.service

What about if use pg_ctl as the postgres user? That will give you
a better idea.

You don't want ot be doing that on a systemd system, but try a
combination of pg_lsclusters and pg_ctlcluster. Might be you need to
shut it down once that way before it realizes it's down,and then start
it back up.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No it doesn’t matter if run with sudo, postgres or even root. Debian actually wraps the command and executes some some initial scripts with different privileges but ends up making sure that Postgres ends up running under the postgres user. I get the same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service -l
Error: could not exec start -D /var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s -o -c config_file="/etc/postgresql/9.4/main/postgresql.conf”

Thanks, though.

-Shawn

Sent from my iPad

On Feb 14, 2017, at 9:47 PM, Shawn Thomas <thomassd@u.washington.edu> wrote:

No it doesn’t matter if run with sudo, postgres or even root. Debian actually wraps the command and executes some some initial scripts with different privileges but ends up making sure that Postgres ends up running under the postgres user. I get the same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service -l
Error: could not exec start -D /var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s -o -c config_file="/etc/postgresql/9.4/main/postgresql.conf”

Thanks, though.

-

which start

Can you run start with -x ?

On 02/14/2017 08:47 PM, Shawn Thomas wrote:

No it doesn’t matter if run with sudo, postgres or even root. Debian
actually wraps the command and executes some some initial scripts with
different privileges but ends up making sure that Postgres ends up
running under the postgres user. I get the same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service
<mailto:postgresql@9.4-main.service> -l
Error: could not exec start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”

So you are talking about:

/etc/init.d/postgresql

which then calls:

/usr/share/postgresql-common/init.d-functions

Or is there another setup on your system?

Any relevant information in the system logs?

Thanks, though.

-Shawn

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes, that’s the correct sequence of scripts. And no there’s not anything really helpful in the system logs.

I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was deleted and cannot be recovered so I need to recover the data from the /var/lib/postgresql/9.4/main directory. I believe this is called a file level recovery. I assume I need to use a fully functional, same version PG (on another machine?) to create a full dump of the data directory. Once I have this I can re-install Postgres on the initial server and read the databases back into it.

Any advice on how to best go about this? The official documentation seems a bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.

-Shawn

On Feb 15, 2017, at 6:35 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 02/14/2017 08:47 PM, Shawn Thomas wrote:

No it doesn’t matter if run with sudo, postgres or even root. Debian
actually wraps the command and executes some some initial scripts with
different privileges but ends up making sure that Postgres ends up
running under the postgres user. I get the same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service
<mailto:postgresql@9.4-main.service> -l
Error: could not exec start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”

So you are talking about:

/etc/init.d/postgresql

which then calls:

/usr/share/postgresql-common/init.d-functions

Or is there another setup on your system?

Any relevant information in the system logs?

Thanks, though.

-Shawn

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/15/2017 08:35 AM, Shawn Thomas wrote:

Yes, that’s the correct sequence of scripts. And no there’s not anything really helpful in the system logs.

I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was deleted and cannot be recovered so I need to recover the data from the /var/lib/postgresql/9.4/main directory. I believe this is called a file level recovery. I assume I need to use a fully functional, same version PG (on another machine?) to create a full dump of the data directory. Once I have this I can re-install Postgres on the initial server and read the databases back into it.

Any advice on how to best go about this? The official documentation seems a bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.

Shawn

As the postgres user:

/usr/lib/postgresql/9.4/bin/pg_ctl -D /var/lib/postgresql/9.4/main start

What returns?

Sincerely,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/15/2017 08:35 AM, Shawn Thomas wrote:

Yes, that’s the correct sequence of scripts. And no there’s not anything really helpful in the system logs.

I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was deleted and cannot be recovered so I need to recover the data from the /var/lib/postgresql/9.4/main directory. I believe this is called a file level recovery. I assume I need to use a fully functional, same version PG (on another machine?) to create a full dump of the data directory. Once I have this I can re-install Postgres on the initial server and read the databases back into it.

I have to believe that if you cannot get the server to start then the
data directory is no shape to recover from. And if the data directory is
good and it is the program files that are corrupted then it would be a
matter of reinstalling Postgres. In either case the most important thing
to do would be to make a copy of the data directory before you do
anything else.

What exactly happened that caused the ssl cert and the pg_dumpall file
to deleted?

In other words what else got deleted?

Any advice on how to best go about this? The official documentation seems a bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.

-Shawn

On Feb 15, 2017, at 6:35 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 02/14/2017 08:47 PM, Shawn Thomas wrote:

No it doesn’t matter if run with sudo, postgres or even root. Debian
actually wraps the command and executes some some initial scripts with
different privileges but ends up making sure that Postgres ends up
running under the postgres user. I get the same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service
<mailto:postgresql@9.4-main.service> -l
Error: could not exec start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”

So you are talking about:

/etc/init.d/postgresql

which then calls:

/usr/share/postgresql-common/init.d-functions

Or is there another setup on your system?

Any relevant information in the system logs?

Thanks, though.

-Shawn

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

/usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory

postgres@pangaea:/usr/lib/postgresql/9.4/bin$ ls -al
total 4008
drwxr-xr-x 2 root root 4096 Feb 9 16:17 .
drwxr-xr-x 3 root root 4096 Feb 9 16:17 ..
-rwxr-xr-x 1 root root 68128 Nov 16 06:53 clusterdb
-rwxr-xr-x 1 root root 68192 Nov 16 06:53 createdb
-rwxr-xr-x 1 root root 63920 Nov 16 06:53 createlang
-rwxr-xr-x 1 root root 72672 Nov 16 06:53 createuser
-rwxr-xr-x 1 root root 63936 Nov 16 06:53 dropdb
-rwxr-xr-x 1 root root 63920 Nov 16 06:53 droplang
-rwxr-xr-x 1 root root 63904 Nov 16 06:53 dropuser
-rwxr-xr-x 1 root root 68416 Nov 16 06:53 pg_basebackup
-rwxr-xr-x 1 root root 351904 Nov 16 06:53 pg_dump
-rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
-rwxr-xr-x 1 root root 30992 Nov 16 06:53 pg_isready
-rwxr-xr-x 1 root root 47600 Nov 16 06:53 pg_receivexlog
-rwxr-xr-x 1 root root 51928 Nov 16 06:53 pg_recvlogical
-rwxr-xr-x 1 root root 154944 Nov 16 06:53 pg_restore
-rwxr-xr-x 1 root root 515320 Nov 16 06:53 psql
-rwxr-xr-x 1 root root 68160 Nov 16 06:53 reindexdb
-rwxr-xr-x 1 root root 72384 Nov 16 06:53 vacuumdb

As I mentioned, this Debian package removes pg_ctl from the bin directory and instead attempts to wrap the pg_ctl functionality in a perl script so that the PG process is integrated with systemd. I really wish they hadn’t, and it’s part of the reason I’m where I’m at.

-Shawn

On Feb 15, 2017, at 8:49 AM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 02/15/2017 08:35 AM, Shawn Thomas wrote:

Yes, that’s the correct sequence of scripts. And no there’s not anything really helpful in the system logs.

I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was deleted and cannot be recovered so I need to recover the data from the /var/lib/postgresql/9.4/main directory. I believe this is called a file level recovery. I assume I need to use a fully functional, same version PG (on another machine?) to create a full dump of the data directory. Once I have this I can re-install Postgres on the initial server and read the databases back into it.

Any advice on how to best go about this? The official documentation seems a bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.

Shawn

As the postgres user:

/usr/lib/postgresql/9.4/bin/pg_ctl -D /var/lib/postgresql/9.4/main start

What returns?

Sincerely,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Shawn Thomas <thomassd@u.washington.edu> writes:

I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was deleted and cannot be recovered so I need to recover the data from the /var/lib/postgresql/9.4/main directory. I believe this is called a file level recovery. I assume I need to use a fully functional, same version PG (on another machine?) to create a full dump of the data directory. Once I have this I can re-install Postgres on the initial server and read the databases back into it.

Seems like the hard way. Rename the data directory out of the way, delete
and reinstall the Postgres packages, and once you've confirmed it works
again, rename the data directory back into place (while the server is
stopped!)

If renaming the old data directory back into place makes it start failing
again, then you've narrowed down the problem to something about the
permissions or contents of the data directory itself or the configuration
files therein.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Feb 15, 2017 at 6:03 PM, Shawn Thomas <thomassd@u.washington.edu>
wrote:

/usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory

postgres@pangaea:/usr/lib/postgresql/9.4/bin$ ls -al
total 4008
drwxr-xr-x 2 root root 4096 Feb 9 16:17 .
drwxr-xr-x 3 root root 4096 Feb 9 16:17 ..
-rwxr-xr-x 1 root root 68128 Nov 16 06:53 clusterdb
-rwxr-xr-x 1 root root 68192 Nov 16 06:53 createdb
-rwxr-xr-x 1 root root 63920 Nov 16 06:53 createlang
-rwxr-xr-x 1 root root 72672 Nov 16 06:53 createuser
-rwxr-xr-x 1 root root 63936 Nov 16 06:53 dropdb
-rwxr-xr-x 1 root root 63920 Nov 16 06:53 droplang
-rwxr-xr-x 1 root root 63904 Nov 16 06:53 dropuser
-rwxr-xr-x 1 root root 68416 Nov 16 06:53 pg_basebackup
-rwxr-xr-x 1 root root 351904 Nov 16 06:53 pg_dump
-rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
-rwxr-xr-x 1 root root 30992 Nov 16 06:53 pg_isready
-rwxr-xr-x 1 root root 47600 Nov 16 06:53 pg_receivexlog
-rwxr-xr-x 1 root root 51928 Nov 16 06:53 pg_recvlogical
-rwxr-xr-x 1 root root 154944 Nov 16 06:53 pg_restore
-rwxr-xr-x 1 root root 515320 Nov 16 06:53 psql
-rwxr-xr-x 1 root root 68160 Nov 16 06:53 reindexdb
-rwxr-xr-x 1 root root 72384 Nov 16 06:53 vacuumdb

As I mentioned, this Debian package removes pg_ctl from the bin directory
and instead attempts to wrap the pg_ctl functionality in a perl script so
that the PG process is integrated with systemd. I really wish they hadn’t,
and it’s part of the reason I’m where I’m at.

pg_ctl is normally present in /usr/lib/postgresql/<version>/bin on a debian
system. If that is gone, somebody removed it, or you didn't install the
"postgresql-9.4" package which provides it. On a 9.4 system:

$ dpkg -S /usr/lib/postgresql/9.4/bin/pg_ctl
postgresql-9.4: /usr/lib/postgresql/9.4/bin/pg_ctl

You could try reinstalling the postgresql-9.4 package and see if it comes
back. The rest of the binaries in that directory seems to be from
postgresql-9.4-client though -- have you actually by mistake uninstalled
the server package completely?

As in, that directory is supposed to have the "postgres" binary which is
the database server and it's not there. So there is no wonder it's not
starting...

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Feb 14, 2017, at 8:47 PM, Shawn Thomas <thomassd@u.washington.edu> wrote:

No it doesn’t matter if run with sudo, postgres or even root. Debian actually wraps the command and executes some some initial scripts with different privileges but ends up making sure that Postgres ends up running under the postgres user. I get the same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service -l
Error: could not exec start -D /var/lib/postgresql/9.4/main -l /var/log/postgresql/postgresql-9.4-main.log -s -o -c config_file="/etc/postgresql/9.4/main/postgresql.conf”

There's a suspicious hole between "exec" and "start" where I'd expect to see the full path to the pg_ctl binary. As though a variable were unset in a script or config file.

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general