Thread: [GENERAL] Can't restart Postgres
I inadvertently deleted the ssl-cert-snakeoil.pem out from under a running Postgres instance (9.4) which caused it to shutdown. 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 complicatesthe debugging and troubleshooting. There doesn't seem to be a way to do anything with Postgres outsided the ofDebian'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. -Shawn
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 shutdown. 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 complicatesthe debugging and troubleshooting. There doesn't seem to be a way to do anything with Postgres outsided the ofDebian'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.
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 Feb 14, 2017, at 11:31 AM, Joshua D. Drake <jd@commandprompt.com> wrote: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.
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.
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.
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 >> > >
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):
@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
On Feb 14, 2017, at 11:52 AM, Magnus Hagander <magnus@hagander.net> wrote: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.--
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
shutdown. 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
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
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
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
On Feb 14, 2017, at 5:12 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: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
-ShawnOn 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 from my iPad
which startNo 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 -lError: 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.-
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
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/maindirectory. 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-installPostgres 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
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/maindirectory. 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-installPostgres 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.
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/maindirectory. 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-installPostgres 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
/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 functionalityin a perl script so that the PG process is integrated with systemd. I really wish they hadn’t, and it’s partof 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/maindirectory. 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-installPostgres 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.
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/maindirectory. 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-installPostgres 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
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...
> 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 someinitial 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 thougha variable were unset in a script or config file. Cheers, Steve
On 02/15/2017 09:03 AM, Shawn Thomas 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 functionalityin a perl script so that the PG process is integrated with systemd. I really wish they hadn’t, and it’s partof the reason I’m where I’m at. Ubuntu uses the same setup and I see it, see below. The Perl script just redirects commands to the correct version of Postgres and uses that versions binaries. aklaver@arkansas:~$ uname -a Linux arkansas 4.8.6-x86_64-linode78 #1 SMP Tue Nov 1 14:51:21 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux aklaver@arkansas:~$ l /usr/lib/postgresql/9.4/bin/ total 8388 drwxr-xr-x 2 root root 4096 Feb 9 07:24 ./ drwxr-xr-x 4 root root 4096 Sep 29 2015 ../ -rwxr-xr-x 1 root root 68096 Feb 8 07:04 clusterdb* -rwxr-xr-x 1 root root 68160 Feb 8 07:04 createdb* -rwxr-xr-x 1 root root 63888 Feb 8 07:04 createlang* -rwxr-xr-x 1 root root 72640 Feb 8 07:04 createuser* -rwxr-xr-x 1 root root 63904 Feb 8 07:04 dropdb* -rwxr-xr-x 1 root root 63888 Feb 8 07:04 droplang* -rwxr-xr-x 1 root root 63872 Feb 8 07:04 dropuser* -rwxr-xr-x 1 root root 114296 Feb 8 07:04 initdb* -rwxr-xr-x 1 root root 26624 Feb 8 07:04 oid2name* -rwxr-xr-x 1 root root 18432 Feb 8 07:04 pg_archivecleanup* -rwxr-xr-x 1 root root 68416 Feb 8 07:04 pg_basebackup* -rwxr-xr-x 1 root root 64600 Feb 8 07:04 pgbench* -rwxr-xr-x 1 root root 30792 Feb 8 07:04 pg_config* -rwxr-xr-x 1 root root 30720 Feb 8 07:04 pg_controldata* -rwxr-xr-x 1 root root 43320 Feb 8 07:04 pg_ctl* -rwxr-xr-x 1 root root 355968 Feb 8 07:04 pg_dump* -rwxr-xr-x 1 root root 89320 Feb 8 07:04 pg_dumpall* -rwxr-xr-x 1 root root 30960 Feb 8 07:04 pg_isready* -rwxr-xr-x 1 root root 47568 Feb 8 07:04 pg_receivexlog* -rwxr-xr-x 1 root root 51928 Feb 8 07:04 pg_recvlogical* -rwxr-xr-x 1 root root 38920 Feb 8 07:04 pg_resetxlog* -rwxr-xr-x 1 root root 154912 Feb 8 07:04 pg_restore* -rwxr-xr-x 1 root root 22536 Feb 8 07:04 pg_standby* -rwxr-xr-x 1 root root 22648 Feb 8 07:04 pg_test_fsync* -rwxr-xr-x 1 root root 14416 Feb 8 07:04 pg_test_timing* -rwxr-xr-x 1 root root 113168 Feb 8 07:04 pg_upgrade* -rwxr-xr-x 1 root root 51672 Feb 8 07:04 pg_xlogdump* -rwxr-xr-x 1 root root 5993920 Feb 8 07:04 postgres* lrwxrwxrwx 1 root root 8 Feb 8 07:04 postmaster -> postgres* -rwxr-xr-x 1 root root 519384 Feb 8 07:04 psql* -rwxr-xr-x 1 root root 68128 Feb 8 07:04 reindexdb* -rwxr-xr-x 1 root root 72352 Feb 8 07:04 vacuumdb* -rwxr-xr-x 1 root root 22528 Feb 8 07:04 vacuumlo* > > -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/maindirectory. 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-installPostgres 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. > -- Adrian Klaver adrian.klaver@aklaver.com
On 02/15/2017 09:03 AM, Shawn Thomas wrote: > /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory > That should have been: lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 16.04.2 LTS Release: 16.04 Codename: xenial -- Adrian Klaver adrian.klaver@aklaver.com
On 02/15/2017 09:03 AM, Shawn Thomas 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 functionalityin a perl script so that the PG process is integrated with systemd. I really wish they hadn’t, and it’s partof the reason I’m where I’m at. Ugh o.k.. I run Ubuntu so I don't have that problem. The thread is a little confusing about what has been tried and what hasn't but: 1. Disable ssl, try and start again 2. Try as postgres: pg_ctlcluster 9.4 stop; pg_ctlcluster 9.4 start 3. Tom has a great suggestion if you are comfortable with those actions. 4. Is that directory listing the total of what is in 9.4/bin? If so... I don't see a postgres binary? 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.
On 02/15/2017 09:17 AM, Adrian Klaver wrote: > On 02/15/2017 09:03 AM, Shawn Thomas wrote: >> /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory >> > > That should have been: > > lsb_release -a > No LSB modules are available. > Distributor ID: Ubuntu > Description: Ubuntu 16.04.2 LTS > Release: 16.04 > Codename: xenial > This is starting to sound like someone inadvertently executed an rm somewhere they shouldn't have (outside of just the original ssl file). 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.
Well that would make more sense of things. I had removed and re-installed the postresql-common package:
and thought that it would leave the main PG package in place. But perhaps I was wrong. I’ll follow Tom’s advice and just re-install everything (saving the old data directory) and hope the new installation can use the old data data directory.
One question about this approach though: the Debian package installation automatically initializes the new data directory and starts PG. If I shut it down and copy the old data directory into the newly installed one, will there be an xlog issue?
-Shawn
On Feb 15, 2017, at 9:09 AM, Magnus Hagander <magnus@hagander.net> wrote: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_ctlpostgresql-9.4: /usr/lib/postgresql/9.4/bin/pg_ctlYou 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...--
On 02/15/2017 09:28 AM, Joshua D. Drake wrote: > On 02/15/2017 09:17 AM, Adrian Klaver wrote: >> On 02/15/2017 09:03 AM, Shawn Thomas wrote: >>> /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory >>> >> >> That should have been: >> >> lsb_release -a >> No LSB modules are available. >> Distributor ID: Ubuntu >> Description: Ubuntu 16.04.2 LTS >> Release: 16.04 >> Codename: xenial >> > > This is starting to sound like someone inadvertently executed an rm > somewhere they shouldn't have (outside of just the original ssl file). Or a defective package(s) upgrade. Either way crucial parts are missing. > > JD > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas <thomassd@u.washington.edu> wrote:
If you had run a reinstall on it, then it would've kept them around.
-- Well that would make more sense of things. I had removed and re-installed the postresql-common package:and thought that it would leave the main PG package in place. But perhaps I was wrong. I’ll follow Tom’s advice and just re-install everything (saving the old data directory) and hope the new installation can use the old data data directory.
If you removed it and then installed it, then the removal would remove all dependent packages and if you then only intalled that one and not the dependencies that would explain it.
If you had run a reinstall on it, then it would've kept them around.
One question about this approach though: the Debian package installation automatically initializes the new data directory and starts PG. If I shut it down and copy the old data directory into the newly installed one, will there be an xlog issue?
You have to copy the xlog along with the database.
Or if you leave it in place where it is, the packages won't initialize a new data directory.
I was responsible for deleting/re-inatalling the cert. I was attempting to get Shibboleth (a federated authentication servicethat also uses the cert) running and didn’t realize that PG relied on it. The dumpAll file wasn’t actually deleted but was empty. I’ll look into that mystery once I get the database back in place. -Shawn > On Feb 15, 2017, at 9:01 AM, Adrian Klaver <adrian.klaver@aklaver.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/maindirectory. 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-installPostgres 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 ifthe 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 anythingelse. > > 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
On 02/15/2017 09:28 AM, Shawn Thomas wrote: > Well that would make more sense of things. I had removed and > re-installed the postresql-common package: > > https://packages.debian.org/jessie/postgresql-common Well that is the glue that holds the pgcluster scheme together. Also when I try it I get: sudo apt-get remove postgresql-common The following packages will be REMOVED: postgresql-9.4 postgresql-9.6 postgresql-common postgresql-contrib-9.4 postgresql-contrib-9.6 postgresql-server-dev-9.4 postgresql-server-dev-9.6 Do you want to continue? [Y/n] Which would explain a lot. > > and thought that it would leave the main PG package in place. But > perhaps I was wrong. I’ll follow Tom’s advice and just re-install > everything (saving the old data directory) and hope the new installation > can use the old data data directory. > > One question about this approach though: the Debian package > installation automatically initializes the new data directory and starts > PG. If I shut it down and copy the old data directory into the newly > installed one, will there be an xlog issue? > > -Shawn > >> On Feb 15, 2017, at 9:09 AM, Magnus Hagander <magnus@hagander.net >> <mailto:magnus@hagander.net>> wrote: >> >> On Wed, Feb 15, 2017 at 6:03 PM, Shawn Thomas >> <thomassd@u.washington.edu <mailto: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/ > -- Adrian Klaver adrian.klaver@aklaver.com
Which would you recommend? Leave the data directory in place and re-install PG or copy it to somewhere else, delete it and then re-install PG?
-Shawn
On Feb 15, 2017, at 9:36 AM, Magnus Hagander <magnus@hagander.net> wrote:On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas <thomassd@u.washington.edu> wrote:--Well that would make more sense of things. I had removed and re-installed the postresql-common package:and thought that it would leave the main PG package in place. But perhaps I was wrong. I’ll follow Tom’s advice and just re-install everything (saving the old data directory) and hope the new installation can use the old data data directory.If you removed it and then installed it, then the removal would remove all dependent packages and if you then only intalled that one and not the dependencies that would explain it.
If you had run a reinstall on it, then it would've kept them around.One question about this approach though: the Debian package installation automatically initializes the new data directory and starts PG. If I shut it down and copy the old data directory into the newly installed one, will there be an xlog issue?You have to copy the xlog along with the database.Or if you leave it in place where it is, the packages won't initialize a new data directory.
On 02/15/2017 09:45 AM, Shawn Thomas wrote: > Which would you recommend? Leave the data directory in place and > re-install PG or copy it to somewhere else, delete it and then > re-install PG? I would copy the data directory somewhere else for safe keeping leaving the original in place. Then reinstall Postgres, the install should leave the original directory alone and you will be ready to go. Should there be an oops you will have the copy as backup. > > -Shawn > >> On Feb 15, 2017, at 9:36 AM, Magnus Hagander <magnus@hagander.net >> <mailto:magnus@hagander.net>> wrote: >> >> On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas >> <thomassd@u.washington.edu <mailto:thomassd@u.washington.edu>> wrote: >> >> Well that would make more sense of things. I had removed and >> re-installed the postresql-common package: >> >> https://packages.debian.org/jessie/postgresql-common >> <https://packages.debian.org/jessie/postgresql-common> >> >> and thought that it would leave the main PG package in place. But >> perhaps I was wrong. I’ll follow Tom’s advice and just re-install >> everything (saving the old data directory) and hope the new >> installation can use the old data data directory. >> >> >> If you removed it and then installed it, then the removal would remove >> all dependent packages and if you then only intalled that one and not >> the dependencies that would explain it. >> >> If you had run a reinstall on it, then it would've kept them around. >> >> >> >> One question about this approach though: the Debian package >> installation automatically initializes the new data directory and >> starts PG. If I shut it down and copy the old data directory into >> the newly installed one, will there be an xlog issue? >> >> >> You have to copy the xlog along with the database. >> >> Or if you leave it in place where it is, the packages won't initialize >> a new data directory. >> >> -- >> Magnus Hagander >> Me: http://www.hagander.net/ >> Work: http://www.redpill-linpro.com/ > -- Adrian Klaver adrian.klaver@aklaver.com
Yes, sadly it does explain things. Your insight has been super helpful though. -Shawn > On Feb 15, 2017, at 9:38 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 02/15/2017 09:28 AM, Shawn Thomas wrote: >> Well that would make more sense of things. I had removed and >> re-installed the postresql-common package: >> >> https://packages.debian.org/jessie/postgresql-common > > Well that is the glue that holds the pgcluster scheme together. Also when I try it I get: > > sudo apt-get remove postgresql-common > > The following packages will be REMOVED: > postgresql-9.4 postgresql-9.6 postgresql-common postgresql-contrib-9.4 postgresql-contrib-9.6 postgresql-server-dev-9.4postgresql-server-dev-9.6 > Do you want to continue? [Y/n] > > Which would explain a lot. > >> >> and thought that it would leave the main PG package in place. But >> perhaps I was wrong. I’ll follow Tom’s advice and just re-install >> everything (saving the old data directory) and hope the new installation >> can use the old data data directory. >> >> One question about this approach though: the Debian package >> installation automatically initializes the new data directory and starts >> PG. If I shut it down and copy the old data directory into the newly >> installed one, will there be an xlog issue? >> >> -Shawn >> >>> On Feb 15, 2017, at 9:09 AM, Magnus Hagander <magnus@hagander.net >>> <mailto:magnus@hagander.net>> wrote: >>> >>> On Wed, Feb 15, 2017 at 6:03 PM, Shawn Thomas >>> <thomassd@u.washington.edu <mailto: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/ >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
Yes, definitely. > On Feb 15, 2017, at 9:49 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 02/15/2017 09:45 AM, Shawn Thomas wrote: >> Which would you recommend? Leave the data directory in place and >> re-install PG or copy it to somewhere else, delete it and then >> re-install PG? > > I would copy the data directory somewhere else for safe keeping leaving the original in place. Then reinstall Postgres,the install should leave the original directory alone and you will be ready to go. Should there be an oops you willhave the copy as backup. > >> >> -Shawn >> >>> On Feb 15, 2017, at 9:36 AM, Magnus Hagander <magnus@hagander.net >>> <mailto:magnus@hagander.net>> wrote: >>> >>> On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas >>> <thomassd@u.washington.edu <mailto:thomassd@u.washington.edu>> wrote: >>> >>> Well that would make more sense of things. I had removed and >>> re-installed the postresql-common package: >>> >>> https://packages.debian.org/jessie/postgresql-common >>> <https://packages.debian.org/jessie/postgresql-common> >>> >>> and thought that it would leave the main PG package in place. But >>> perhaps I was wrong. I’ll follow Tom’s advice and just re-install >>> everything (saving the old data directory) and hope the new >>> installation can use the old data data directory. >>> >>> >>> If you removed it and then installed it, then the removal would remove >>> all dependent packages and if you then only intalled that one and not >>> the dependencies that would explain it. >>> >>> If you had run a reinstall on it, then it would've kept them around. >>> >>> >>> >>> One question about this approach though: the Debian package >>> installation automatically initializes the new data directory and >>> starts PG. If I shut it down and copy the old data directory into >>> the newly installed one, will there be an xlog issue? >>> >>> >>> You have to copy the xlog along with the database. >>> >>> Or if you leave it in place where it is, the packages won't initialize >>> a new data directory. >>> >>> -- >>> Magnus Hagander >>> Me: http://www.hagander.net/ >>> Work: http://www.redpill-linpro.com/ >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
Just wanted to follow up that re-installing Postgres worked (well almost—I did have to reset the permissions and ownershipon the key and pem file). Thanks so much for all the help. -Shawn > On Feb 15, 2017, at 9:49 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 02/15/2017 09:45 AM, Shawn Thomas wrote: >> Which would you recommend? Leave the data directory in place and >> re-install PG or copy it to somewhere else, delete it and then >> re-install PG? > > I would copy the data directory somewhere else for safe keeping leaving the original in place. Then reinstall Postgres,the install should leave the original directory alone and you will be ready to go. Should there be an oops you willhave the copy as backup. > >> >> -Shawn >> >>> On Feb 15, 2017, at 9:36 AM, Magnus Hagander <magnus@hagander.net >>> <mailto:magnus@hagander.net>> wrote: >>> >>> On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas >>> <thomassd@u.washington.edu <mailto:thomassd@u.washington.edu>> wrote: >>> >>> Well that would make more sense of things. I had removed and >>> re-installed the postresql-common package: >>> >>> https://packages.debian.org/jessie/postgresql-common >>> <https://packages.debian.org/jessie/postgresql-common> >>> >>> and thought that it would leave the main PG package in place. But >>> perhaps I was wrong. I’ll follow Tom’s advice and just re-install >>> everything (saving the old data directory) and hope the new >>> installation can use the old data data directory. >>> >>> >>> If you removed it and then installed it, then the removal would remove >>> all dependent packages and if you then only intalled that one and not >>> the dependencies that would explain it. >>> >>> If you had run a reinstall on it, then it would've kept them around. >>> >>> >>> >>> One question about this approach though: the Debian package >>> installation automatically initializes the new data directory and >>> starts PG. If I shut it down and copy the old data directory into >>> the newly installed one, will there be an xlog issue? >>> >>> >>> You have to copy the xlog along with the database. >>> >>> Or if you leave it in place where it is, the packages won't initialize >>> a new data directory. >>> >>> -- >>> Magnus Hagander >>> Me: http://www.hagander.net/ >>> Work: http://www.redpill-linpro.com/ >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
On 02/15/2017 03:09 PM, Shawn Thomas wrote: > Just wanted to follow up that re-installing Postgres worked (well almost—I did have to reset the permissions and ownershipon the key and pem file). > > Thanks so much for all the help. > That's what we are here for :D Sincerely, 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.