server will not start (Debian)

On Tue, Dec 10, 2019 at 06:48:23AM -0500, stan wrote:

I just went to do soem work on our test/sandbox instance. I had upgraded
this ssytem to V12 a few days agao. I found that it was not running:

Here is the last thing in the log:

2019-12-06 14:16:06.149 EST [3764] LOG: received fast shutdown request
2019-12-06 14:16:06.154 EST [3764] LOG: aborting any active transactions
2019-12-06 14:16:06.160 EST [3764] LOG: background worker "logical
replication launcher" (PID 3771) exited with exit code 1
2019-12-06 14:16:06.162 EST [3766] LOG: shutting down
2019-12-06 14:16:06.204 EST [3764] LOG: database system is shut down

This was in the logfile that was one rotation back. The current one is
zero bytes:

pg_lsclsuters shows:

Ver Cluster Port Status Owner Data directory Log file
12 main 5432 down postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

systemctl status postgresql shows:

root@stantest:/var/log/postgresql# systemctl status postgresql
��� postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor
prese
Active: active (exited) since Fri 2019-12-06 14:22:13 EST; 3 days ago
Process: 371 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Process: 5555 ExecReload=/bin/true (code=exited,
status=0/SUCCESS)
Main PID: 371 (code=exited, status=0/SUCCESS)

Dec 10 06:41:19 stantest systemd[1]: Reloading PostgreSQL RDBMS.
Dec 10 06:41:19 stantest systemd[1]: Reloaded PostgreSQL RDBMS.
Warning: Journal has been rotated since unit was started. Log output is incomple

What do I need to do to get it started AND fix whatever caused this
unexpected shutdown?

More info:

As user posgres I cna start the server with:

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

Then I can connect to it. But sysctl does not start the instance.

I am also getting this error message:

/var/run/postgresql/12-main.pg_stat_tmp/global.tmp": No such file or
directory
2019-12-10 08:35:02.360 EST [786] LOG: could not open temporary
statistics file "/var/run/postgresql/12-main.pg_stat_tmp/global.tmp": No
such file or directory

But if I create that directory manually I cna start the server,

As a reminder, this instance was recently upgarded from V11 to V12, if
thta is significant.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:

Hi Stan

Check security make sure V12 postgres has the correct credentials....

OK,

postgres@stantest:/var/run/postgresql$ ls -ld
drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .

Looks correct to me.

This sundirectory 12-main.pg_stat_tmp keeps getting deleted.

If I create it by hand as user psotgres, the DB starts up when I run:

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

But tring to start it with /etc/ini.d/postgresql start fails

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On 12/10/19 7:32 AM, stan wrote:

On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:

Hi Stan

Check security make sure V12 postgres has the correct credentials....

OK,

postgres@stantest:/var/run/postgresql$ ls -ld
drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .

Looks correct to me.

This sundirectory 12-main.pg_stat_tmp keeps getting deleted.

If I create it by hand as user psotgres, the DB starts up when I run:

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

But tring to start it with /etc/ini.d/postgresql start fails

How did you install Postgres 12?

Why are you using the file in init.d/ instead of pg_ctlcluster?

--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/12/2019 16:11, Adrian Klaver wrote:

On 12/10/19 7:32 AM, stan wrote:

On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:

Hi Stan

Check security make sure V12 postgres  has the correct credentials....

OK,

postgres@stantest:/var/run/postgresql$ ls -ld
drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .

Looks correct to me.

This sundirectory 12-main.pg_stat_tmp keeps getting deleted.

If I create it by hand as user psotgres, the DB starts up when I run:

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

But tring to start it with /etc/ini.d/postgresql start fails

How did you install Postgres 12?

Why are you using the file in init.d/ instead of pg_ctlcluster?

Or indeed systemctl? :-)

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

will the service start from the command line without creating the stats
directory???

to my understanding after every shutdown pg_stat_tmp is deleted and
recreated during startup

starting manually, yet the service will not normally points to a
credentials or path issue
https://www.postgresql.org/docs/current/runtime-config-statistics.html

On Tue, Dec 10, 2019 at 10:32 AM stan <stanb@panix.com> wrote:

On Tue, Dec 10, 2019 at 04:13:02PM +0000, Ray O'Donnell wrote:

On 10/12/2019 16:11, Adrian Klaver wrote:

On 12/10/19 7:32 AM, stan wrote:

On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:

Hi Stan

Check security make sure V12 postgres?? has the correct credentials....

OK,

postgres@stantest:/var/run/postgresql$ ls -ld
drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .

Looks correct to me.

This sundirectory 12-main.pg_stat_tmp keeps getting deleted.

If I create it by hand as user psotgres, the DB starts up when I run:

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

But tring to start it with /etc/ini.d/postgresql start fails

How did you install Postgres 12?

sudo apt-get install pointed at:

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

Why are you using the file in init.d/ instead of pg_ctlcluster?

Or indeed systemctl? :-)

I tried that also. did not remeber

also:

postgres@ica-db:~$ pg_ctlcluster 12 main start
Warning: the cluster will not be running as a systemd service. Consider
using systemctl:
sudo systemctl start postgresql@12-main
Cluster is already running.

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On Tue, Dec 10, 2019 at 04:13:02PM +0000, Ray O'Donnell wrote:

On 10/12/2019 16:11, Adrian Klaver wrote:

On 12/10/19 7:32 AM, stan wrote:

On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:

Hi Stan

Check security make sure V12 postgres?? has the correct credentials....

OK,

postgres@stantest:/var/run/postgresql$ ls -ld
drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .

Looks correct to me.

This sundirectory 12-main.pg_stat_tmp keeps getting deleted.

If I create it by hand as user psotgres, the DB starts up when I run:

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

But tring to start it with /etc/ini.d/postgresql start fails

How did you install Postgres 12?

Why are you using the file in init.d/ instead of pg_ctlcluster?

Or indeed systemctl? :-)

OK, here is the latets. If I do sudo systemctl start postgresql@12-main

the instance starts and works fine.

However, if I reboot the machine, the instance is not started.

Thoughts as to what to check next, appreciated.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

On 12/10/19 8:24 AM, stan wrote:

On Tue, Dec 10, 2019 at 04:13:02PM +0000, Ray O'Donnell wrote:

On 10/12/2019 16:11, Adrian Klaver wrote:

On 12/10/19 7:32 AM, stan wrote:

On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:

Hi Stan

Check security make sure V12 postgres?? has the correct credentials....

OK,

postgres@stantest:/var/run/postgresql$ ls -ld
drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .

Looks correct to me.

This sundirectory 12-main.pg_stat_tmp keeps getting deleted.

If I create it by hand as user psotgres, the DB starts up when I run:

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

But tring to start it with /etc/ini.d/postgresql start fails

How did you install Postgres 12?

sudo apt-get install pointed at:

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

Why are you using the file in init.d/ instead of pg_ctlcluster?

Or indeed systemctl? :-)

I tried that also. did not remeber

also:

postgres@ica-db:~$ pg_ctlcluster 12 main start
Warning: the cluster will not be running as a systemd service. Consider
using systemctl:
sudo systemctl start postgresql@12-main
Cluster is already running.

You need to do the above as root:

aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
11 main 5431 down postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12 main 5432 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

aklaver@arkansas:~$ pg_ctlcluster 11 main start
Warning: the cluster will not be running as a systemd service. Consider
using systemctl:
sudo systemctl start postgresql@11-main
Error: You must run this program as the cluster owner (postgres) or root

aklaver@arkansas:~$ sudo pg_ctlcluster 11 main start
[sudo] password for aklaver:

aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
11 main 5431 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12 main 5432 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

Also the cluster is already running.
To confirm what you have running do:

ps ax | grep post

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/10/19 9:03 AM, stan wrote:

On Tue, Dec 10, 2019 at 04:13:02PM +0000, Ray O'Donnell wrote:

On 10/12/2019 16:11, Adrian Klaver wrote:

On 12/10/19 7:32 AM, stan wrote:

On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:

Hi Stan

Check security make sure V12 postgres?? has the correct credentials....

OK,

postgres@stantest:/var/run/postgresql$ ls -ld
drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .

Looks correct to me.

This sundirectory 12-main.pg_stat_tmp keeps getting deleted.

If I create it by hand as user psotgres, the DB starts up when I run:

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

But tring to start it with /etc/ini.d/postgresql start fails

How did you install Postgres 12?

Why are you using the file in init.d/ instead of pg_ctlcluster?

Or indeed systemctl? :-)

OK, here is the latets. If I do sudo systemctl start postgresql@12-main

the instance starts and works fine.

However, if I reboot the machine, the instance is not started.

Thoughts as to what to check next, appreciated.

In /etc/postgresql/12/main there is file start.conf with:

# Automatic startup configuration
# auto: automatically start the cluster
# manual: manual startup with pg_ctlcluster/postgresql@.service only
# disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.

auto

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2019-12-10 12:03:30 -0500, stan wrote:

OK, here is the latets. If I do sudo systemctl start postgresql@12-main

the instance starts and works fine.

However, if I reboot the machine, the instance is not started.

Check whether the service is enabled in systemd:

# systemctl status postgresql@12-main.service
● postgresql@12-main.service - PostgreSQL Cluster 12-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
^^^^^^^

If it isn't, enable it:

# systemctl enable postgresql@12-main.service

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"