Re: psql: FATAL: the database system is starting up

[Adrian Klaver]

On 5/27/19 9:59 PM, Tom K wrote:

Hey Guy's,


I'm running Patroni w/ PostgreSQL 10, ETCD, Haproxy and Keepalived on 3 
RHEL 7.6 VM's.  Every now and then the underlying storage crashes taking 
out the cluster.  On recovery, PostgreSQL tends to come up while other 
databases just blow up.  That is until now.


The above is one master and two standby servers connected via streaming 
replication, correct?


Is there WAL archiving set up?




After the most recent crash 2-3 weeks ago, the cluster is now running 
into this message but I'm not able to make heads or tails out of why 
it's throwing this:


So you have not been able to run the cluster the past 2-3 weeks or is 
that  more recent?


When you refer to history files below are you talking about WAL files or 
something else?


Is this:

"recovery command file "recovery.conf" specified neither 
primary_conninfo nor restore_command"


true?





|-bash-4.2$/usr/pgsql-10/bin/postgres -D /data/patroni 
--config-file=/data/patroni/postgresql.conf2019-05-2308:26:34.561EDT 
[10101]LOG:listening onIPv4 address "192.168.0.108",port 
54322019-05-2308:26:34.565EDT [10101]LOG:listening onUnix socket 
"./.s.PGSQL.5432"2019-05-2308:26:34.591EDT [10101]LOG:redirecting log 
output tologging collector process 2019-05-2308:26:34.591EDT 
[10101]HINT:Future log output will appear indirectory 
"log".^C-bash-4.2/usr/pgsql-10/bin/postgres -D /data/patroni 
--config-file=/data/patroni/postgresql.conf --hot_standby=on 
--listen_addresses=192.168.0.108 --max_worker_processes=8 
--max_locks_per_transaction=64 --wal_level=replica 
--cluster_name=postgres --wal_log_hints=on --max_wal_senders=10 
--track_commit_timestamp=off --max_prepared_transactions=0 --port=5432 
--max_replication_slots=10 
--max_connections=1002019-05-2308:26:45.707EDT [10105]LOG:listening 
onIPv4 address "192.168.0.108",port 54322019-05-2308:26:45.715EDT 
[10105]LOG:listening onUnix socket 
"./.s.PGSQL.5432"2019-05-2308:26:45.740EDT [10105]LOG:redirecting log 
output tologging collector process 2019-05-2308:26:45.740EDT 
[10105]HINT:Future log output will appear indirectory 
"log".^C-bash-4.2$/usr/pgsql-10/bin/postgres -D /data/patroni 
--config-file=/data/patroni/postgresql.conf --hot_standby=on 
--listen_addresses=192.168.0.1 --max_worker_processes=8 
--max_locks_per_transaction=64 --wal_level=replica 
--cluster_name=postgres --wal_log_hints=on --max_wal_senders=10 
--track_commit_timestamp=off --max_prepared_transactions=0 --port=5432 
--max_replication_slots=10 
--max_connections=1002019-05-2308:29:36.014EDT [10188]LOG:listening 
onIPv4 address "192.168.0.108",port 54322019-05-2308:29:36.017EDT 
[10188]LOG:listening onUnix socket 
"./.s.PGSQL.5432"2019-05-2308:29:36.040EDT [10188]LOG:redirecting log 
output tologging collector process 2019-05-2308:29:36.040EDT 
[10188]HINT:Future log output will appear indirectory "log".|


The database logs only print out this:

|2019-05-2308:25:44.476EDT [10088]HINT:The databaseserver will regularly 
poll the pg_wal subdirectory tocheckforfiles placed 
there.2019-05-2308:25:44.477EDT [10088]LOG:entering standby mode 
2019-05-2308:25:50.209EDT [10086]LOG:received fast shutdownrequest 
2019-05-2308:25:50.215EDT [10089]LOG:shutting down 
2019-05-2308:25:50.221EDT [10086]LOG:databasesystem isshut down 
2019-05-2308:26:11.560EDT [10094]LOG:databasesystem was shut down 
inrecovery at 2019-05-2308:25:50EDT 2019-05-2308:26:11.560EDT 
[10094]WARNING:recovery command file"recovery.conf"specified neither 
primary_conninfo nor restore_command 2019-05-2308:26:11.560EDT 
[10094]HINT:The databaseserver will regularly poll the pg_wal 
subdirectory tocheckforfiles placed there.2019-05-2308:26:11.562EDT 
[10094]LOG:entering standby mode 2019-05-2308:26:17.817EDT 
[10092]LOG:received fast shutdownrequest 2019-05-2308:26:17.824EDT 
[10095]LOG:shutting down 2019-05-2308:26:17.831EDT 
[10092]LOG:databasesystem isshut down 2019-05-2308:26:34.596EDT 
[10103]LOG:databasesystem was shut down inrecovery at 
2019-05-2308:26:17EDT 2019-05-2308:26:34.596EDT [10103]WARNING:recovery 
command file"recovery.conf"specified neither primary_conninfo nor 
restore_command 2019-05-2308:26:34.596EDT [10103]HINT:The databaseserver 
will regularly poll the pg_wal subdirectory tocheckforfiles placed 
there.2019-05-2308:26:34.598EDT [10103]LOG:entering standby mode 
2019-05-2308:26:43.025EDT [10101]LOG:received fast shutdownrequest 
2019-05-2308:26:43.032EDT [10104]LOG:shutting down 
2019-05-2308:26:43.038EDT [10101]LOG:databasesystem isshut down 
2019-05-2308:26:45.748EDT [10107]LOG:databasesystem was shut down 
inrecovery at 2019-05-2308:26:43EDT 2019-05-2308:26:45.748EDT 
[10107]WARNING:recovery command file"recovery.conf"specified neither 
primary_conninfo nor restore_command 2019-05-2308:26:45.748EDT 
[10107]HINT:The databaseserver will regularly poll the pg_wal 
subdirectory tocheckforfiles placed there.|


and when connecting, I get this cryptic message:

|[root@psql01 log]#psql -U postgres -h 192.168.0.108

Alternate methods for multiple rows input/output to a function.

[RAJIN RAJ K]

--> Function ' filter_id ' filters the ID's based on some conditions.
--> Input is set of ID's. (Not directly taking the input since there is no
provision to pass multiple rows to a function)

create function filter_id()
return table (id bigint)
begin

--> Assuming input table is already created #temp_input_id

retun query as select id
from tbl a
inner join
#temp_input_id b on (a.id = b.id)
where a.;

end;


--> Calling Function:

create function caller()
return table (id bigint,col1 bigint, col2 bigint)
begin

--> do some processing

--> Find out the input id's for filtering.

--> Create temp table for providing input for the filtering function

create temp table #TEMP1
as select id from tbla;
(Cannot move the input id logic to  filter_function)

--> calling the filter function
create temp table #TEMP2
as select * from filter_id(); --> This is a generic function used in many
functions.


return query
as select a.*
from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2;
end;


Is there any alternate way of achieving this? Passing multiple records to a
function im creating a temp table before invoking the function.
For receiving an output of multiple rows i'm creating a temp table to reuse
further in the code.

Can this be done using Refcursor? Is it possible to convert refcursor to a
temp table and use it as normal  table in query?

Re: Alternate methods for multiple rows input/output to a function.

[Adrian Klaver]

On 5/28/19 7:36 AM, RAJIN RAJ K wrote:

--> Function ' filter_id ' filters the ID's based on some conditions.
--> Input is set of ID's. (Not directly taking the input since there is 
no provision to pass multiple rows to a function)


To be honest I cannot follow what you are trying to achieve below. I do 
have one suggestion as to creating temp tables.


Why not use a  CTE:

https://www.postgresql.org/docs/11/queries-with.html

in the function to build a 'temp' table on the fly?



create function filter_id()
return table (id bigint)
begin

--> Assuming input table is already created #temp_input_id

retun query as select id
from tbl a
inner join
#temp_input_id b on (a.id  = b.id )
where a.;

end;


--> Calling Function:

create function caller()
return table (id bigint,col1 bigint, col2 bigint)
begin

--> do some processing

--> Find out the input id's for filtering.

--> Create temp table for providing input for the filtering function

create temp table #TEMP1
as select id from tbla;
(Cannot move the input id logic to  filter_function)

--> calling the filter function
create temp table #TEMP2
as select * from filter_id(); --> This is a generic function used in 
many functions.



return query
as select a.*
from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2;
end;


Is there any alternate way of achieving this? Passing multiple records 
to a function im creating a temp table before invoking the function.
For receiving an output of multiple rows i'm creating a temp table to 
reuse further in the code.


Can this be done using Refcursor? Is it possible to convert refcursor to 
a temp table and use it as normal  table in query?






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Alternate methods for multiple rows input/output to a function.

[Adrian Klaver]

On 5/28/19 8:06 AM, RAJIN RAJ K wrote:

Please reply to list also.
Ccing list.


Thanks for the response.

CTE is not useful in my case. Here i want to pass the table to a 
function and get the filtered results back from the function.

I tried few but not use full.
1. Pass table input --> Ref cursor is the only option but which again 
require loop to fetch the records. (FETCH ALL results cannot be stored 
in a variable)
     Here im creating temp table withe required input data before the 
function call.


I'm going to take a stab at this though I do not entirely follow the 
logic. Definitely not tested:


1) create function filter_id(tbl_name varchar)
return table (id bigint)
begin

--> Assuming input table is already created #temp_input_id

return query EXECUTE format('select id '
'from tbl a '
'inner join'
'%I b on (a.id = b.id)'
'where a.', tbl_name);

end;

2) In calling function:

WITH temp_tbl AS (select id from tbla...
), filter_tbl AS (select * from filter_id(temp_bl))
select a.*
from tb3 a inner join tb4 inner join tb 5 inner join filter_tbl;






On Tue, May 28, 2019 at 8:29 PM Adrian Klaver > wrote:


On 5/28/19 7:36 AM, RAJIN RAJ K wrote:
 > --> Function ' filter_id ' filters the ID's based on some conditions.
 > --> Input is set of ID's. (Not directly taking the input since
there is
 > no provision to pass multiple rows to a function)

To be honest I cannot follow what you are trying to achieve below. I do
have one suggestion as to creating temp tables.

Why not use a  CTE:

https://www.postgresql.org/docs/11/queries-with.html

in the function to build a 'temp' table on the fly?

 >
 > create function filter_id()
 > return table (id bigint)
 > begin
 >
 > --> Assuming input table is already created #temp_input_id
 >
 > retun query as select id
 > from tbl a
 > inner join
 > #temp_input_id b on (a.id   = b.id
 )
 > where a.;
 >
 > end;
 >
 >
 > --> Calling Function:
 >
 > create function caller()
 > return table (id bigint,col1 bigint, col2 bigint)
 > begin
 >
 > --> do some processing
 >
 > --> Find out the input id's for filtering.
 >
 > --> Create temp table for providing input for the filtering function
 >
 > create temp table #TEMP1
 > as select id from tbla;
 > (Cannot move the input id logic to  filter_function)
 >
 > --> calling the filter function
 > create temp table #TEMP2
 > as select * from filter_id(); --> This is a generic function used in
 > many functions.
 >
 >
 > return query
 > as select a.*
 > from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2;
 > end;
 >
 >
 > Is there any alternate way of achieving this? Passing multiple
records
 > to a function im creating a temp table before invoking the function.
 > For receiving an output of multiple rows i'm creating a temp
table to
 > reuse further in the code.
 >
 > Can this be done using Refcursor? Is it possible to convert
refcursor to
 > a temp table and use it as normal  table in query?
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Julie Nishimura]

Adrian, the current nagios alerting does the following:

postgres=# SELECT count(*) from pg_freespacemap_relations;
 count
---
 7
(1 row)

and this is the snippet from our config:
max_fsm_pages = 600
# (change requires restart)
max_fsm_relations = 8

So, we are pretty much at 100%. We have decided to increase max_fsm_relations = 
9 and restart.


To do the right settings for this value, should I run this query in all dbs on 
the server:
> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')

?

Thanks for your help



From: Adrian Klaver 
Sent: Friday, May 24, 2019 10:22 AM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 
79569 of 8 (99%)

On 5/24/19 10:16 AM, Julie Nishimura wrote:
> Adrian, this value was set in config file, and alerting comes from
> monitoring.

Yes, but what is the monitoring actually doing to get that value?

>
> Would it be right query to count objects in each database (there are 75
> dbs on this server totaling close to 20 tb):
>
> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')
>
> ?
>
> Thanks!
>
> 
> *From:* Adrian Klaver 
> *Sent:* Friday, May 24, 2019 7:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations
> used: 79569 of 8 (99%)
> On 5/23/19 11:57 PM, Julie Nishimura wrote:
>> Hello,
>> We have an issue with fsm_relations utilization reaching 99%, I was able
>
> How are you arriving at the above percentage?
>
> How many tables/indexes do you have in the database(s)?
>
>> to vacuum a handful of tables, but it wasn't enough to make a noticeable
>> difference. I think at this point we will need to increase the number of
>> fsm_relations from 80,000 to 100,000 which will require a restart.
>> Because there aren't any more dead rows to delete. I confirmed this by
>> connecting to each db and running the following query:
>> SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by
>> n_dead_tup desc
>> daily_mail_shared_state_cddt_3588-
>> relname | n_live_tup | n_dead_tup
>> ++
>> article_errors | 0 | 0
>> article_names | 3375193 | 0
>> indexdefs | 0 | 0
>> tabledefs | 0 | 0
>>
>> Above output, shows n_dead_tup is zeroed out, this makes me believe that
>> we need to increase the number of fsm relations to a number between 90k
>> and 100k.But I might be wrong, need your advice.
>>
>> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)
>>
>> Thanks!
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Julie Nishimura]

What is the impact of fsm_relatiosn being maxed out?


From: Julie Nishimura 
Sent: Tuesday, May 28, 2019 11:11 AM
To: Adrian Klaver; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 
79569 of 8 (99%)

Adrian, the current nagios alerting does the following:

postgres=# SELECT count(*) from pg_freespacemap_relations;
 count
---
 7
(1 row)

and this is the snippet from our config:
max_fsm_pages = 600
# (change requires restart)
max_fsm_relations = 8

So, we are pretty much at 100%. We have decided to increase max_fsm_relations = 
9 and restart.


To do the right settings for this value, should I run this query in all dbs on 
the server:
> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')

?

Thanks for your help



From: Adrian Klaver 
Sent: Friday, May 24, 2019 10:22 AM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 
79569 of 8 (99%)

On 5/24/19 10:16 AM, Julie Nishimura wrote:
> Adrian, this value was set in config file, and alerting comes from
> monitoring.

Yes, but what is the monitoring actually doing to get that value?

>
> Would it be right query to count objects in each database (there are 75
> dbs on this server totaling close to 20 tb):
>
> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')
>
> ?
>
> Thanks!
>
> 
> *From:* Adrian Klaver 
> *Sent:* Friday, May 24, 2019 7:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations
> used: 79569 of 8 (99%)
> On 5/23/19 11:57 PM, Julie Nishimura wrote:
>> Hello,
>> We have an issue with fsm_relations utilization reaching 99%, I was able
>
> How are you arriving at the above percentage?
>
> How many tables/indexes do you have in the database(s)?
>
>> to vacuum a handful of tables, but it wasn't enough to make a noticeable
>> difference. I think at this point we will need to increase the number of
>> fsm_relations from 80,000 to 100,000 which will require a restart.
>> Because there aren't any more dead rows to delete. I confirmed this by
>> connecting to each db and running the following query:
>> SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by
>> n_dead_tup desc
>> daily_mail_shared_state_cddt_3588-
>> relname | n_live_tup | n_dead_tup
>> ++
>> article_errors | 0 | 0
>> article_names | 3375193 | 0
>> indexdefs | 0 | 0
>> tabledefs | 0 | 0
>>
>> Above output, shows n_dead_tup is zeroed out, this makes me believe that
>> we need to increase the number of fsm relations to a number between 90k
>> and 100k.But I might be wrong, need your advice.
>>
>> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)
>>
>> Thanks!
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Andreas Kretschmer]

On 28 May 2019 20:20:10 CEST, Julie Nishimura  wrote:
>What is the impact of fsm_relatiosn being maxed out?

https://www.postgresql.org/docs/8.2/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Please no top-posting with fullquote.


Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Adrian Klaver]

On 5/28/19 11:20 AM, Julie Nishimura wrote:

What is the impact of fsm_relatiosn being maxed out?



It has been awhile since I thought about this as these settings are no 
longer relevant as of 8.4:


https://www.postgresql.org/docs/8.4/release-8-4.html

"

Track free space in separate per-relation "fork" files (Heikki)

Free space discovered by VACUUM is now recorded in *_fsm files, rather 
than in a fixed-sized shared memory area. The max_fsm_pages and 
max_fsm_relations settings have been removed, greatly simplifying 
administration of free space management.

"

To get to your question, I would take a look at:

https://www.postgresql.org/docs/8.3/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Pay attention to the embedded link in the above:

https://www.postgresql.org/docs/8.3/kernel-resources.html#SYSVIPC


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Julie Nishimura]

Adrian, I am trying to avoid to do any tweaking to this legacy system that 
nobody knows well (we inherited it recently).
Do you think it might help if we possibly drop old tables (I assume their 
indices will be removed too), so the overall number of objects will go down? 
Thanks a lot


From: Adrian Klaver 
Sent: Tuesday, May 28, 2019 12:43 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 
79569 of 8 (99%)

On 5/28/19 11:20 AM, Julie Nishimura wrote:
> What is the impact of fsm_relatiosn being maxed out?
>

It has been awhile since I thought about this as these settings are no
longer relevant as of 8.4:

https://www.postgresql.org/docs/8.4/release-8-4.html

"

Track free space in separate per-relation "fork" files (Heikki)

Free space discovered by VACUUM is now recorded in *_fsm files, rather
than in a fixed-sized shared memory area. The max_fsm_pages and
max_fsm_relations settings have been removed, greatly simplifying
administration of free space management.
"

To get to your question, I would take a look at:

https://www.postgresql.org/docs/8.3/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Pay attention to the embedded link in the above:

https://www.postgresql.org/docs/8.3/kernel-resources.html#SYSVIPC


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Alvaro Herrera]

On 2019-May-28, Julie Nishimura wrote:

> Adrian, I am trying to avoid to do any tweaking to this legacy system that 
> nobody knows well (we inherited it recently).
> Do you think it might help if we possibly drop old tables (I assume their 
> indices will be removed too), so the overall number of objects will go down? 
> Thanks a lot

Having insufficient max_fsm_relations causes severe problems, so don't
if you can avoid it.  I would certainly recommend increasing it.

Note that increasing max_fsm_relations requires that you have a large
enough shared memory allowance in the operating system; if you're too
close to the limit and try to restart with the increased setting, the
service may fail to start.  However, unless you're on something weird
that requires recompiling the kernel to update that limit, it should be
fairly simple to update it.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Adrian Klaver]

On 5/28/19 2:53 PM, Julie Nishimura wrote:
Please post to list also.
Ccing list

I am sorry, did not mean to send it yet.
#--
# RESOURCE USAGE (except WAL)
#--

# - Memory -

shared_buffers = 16GB                   # min 128kB or max_connections*16kB
                                         # (change requires restart)
temp_buffers = 8MB                      # min 800kB
#max_prepared_transactions = 5          # can be 0 or more
                                         # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared 
memory

# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 256MB                                # min 64kB
maintenance_work_mem = 512MB            # min 1MB
max_stack_depth = 4MB                   # min 100kB

# - Free Space Map -

max_fsm_pages = 600
                                         # (change requires restart)
max_fsm_relations = 8
#--
# WRITE AHEAD LOG
#--

# - Settings -
wal_buffers = 4096kB

# - Free Space Map -

max_fsm_pages = 600
                                         # (change requires restart)
max_fsm_relations = 8

Seems like the system has 98 gb, and shared_buffers set to 16 gb, so if 
we increase max_fsm_relations to 10, should be still enough memory 
to start, right?


Thank you!


*From:* Julie Nishimura 
*Sent:* Tuesday, May 28, 2019 2:49 PM
*To:* Adrian Klaver
*Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
used: 79569 of 8 (99%)



Adrian,
Thanks for your help. It seems like the system has 98 gb memory on the 
server overall


max_connections = 300


*From:* Julie Nishimura 
*Sent:* Tuesday, May 28, 2019 2:34 PM
*To:* Adrian Klaver
*Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
used: 79569 of 8 (99%)

We haven't yet, still waiting for the approval from our management


*From:* Adrian Klaver 
*Sent:* Tuesday, May 28, 2019 2:11 PM
*To:* Julie Nishimura
*Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
used: 79569 of 8 (99%)

On 5/28/19 2:03 PM, Julie Nishimura wrote:
Adrian, I am trying to avoid to do any tweaking to this legacy system 
that nobody knows well (we inherited it recently).


Yeah, but you already tweaked it, so it is useful to know what the
repercussions might be.

Do you think it might help if we possibly drop old tables (I assume 
their indices will be removed too), so the overall number of objects 
will go down? Thanks a lot


Yes. Just not sure when that will happen.




*From:* Adrian Klaver 
*Sent:* Tuesday, May 28, 2019 12:43 PM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
used: 79569 of 8 (99%)

On 5/28/19 11:20 AM, Julie Nishimura wrote:

What is the impact of fsm_relatiosn being maxed out?



It has been awhile since I thought about this as these settings are no
longer relevant as of 8.4:

https://www.postgresql.org/docs/8.4/release-8-4.html

"

Track free space in separate per-relation "fork" files (Heikki)

Free space discovered by VACUUM is now recorded in *_fsm files, rather
than in a fixed-sized shared memory area. The max_fsm_pages and
max_fsm_relations settings have been removed, greatly simplifying
administration of free space management.
"

To get to your question, I would take a look at:

https://www.postgresql.org/docs/8.3/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Pay attention to the embedded link in the above:

https://www.postgresql.org/docs/8.3/kernel-resources.html#SYSVIPC


--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: psql: FATAL: the database system is starting up

[Tom K]

On Tue, May 28, 2019 at 9:53 AM Adrian Klaver 
wrote:

> On 5/27/19 9:59 PM, Tom K wrote:
> > Hey Guy's,
> >
> >
> > I'm running Patroni w/ PostgreSQL 10, ETCD, Haproxy and Keepalived on 3
> > RHEL 7.6 VM's.  Every now and then the underlying storage crashes taking
> > out the cluster.  On recovery, PostgreSQL tends to come up while other
> > databases just blow up.  That is until now.
>
> The above is one master and two standby servers connected via streaming
> replication, correct?


> Is there WAL archiving set up?
>

Correct.  Master election occurs through Patroni.  WAL level is set to:

wal_level = 'replica'

So no archiving.


>
>
> >
> > After the most recent crash 2-3 weeks ago, the cluster is now running
> > into this message but I'm not able to make heads or tails out of why
> > it's throwing this:
>
> So you have not been able to run the cluster the past 2-3 weeks or is
> that  more recent?
>

Haven't been able to bring this PostgresSQL cluster up ( run the cluster )
since 2-3 weeks ago.  Tried quite a few combinations of options to recover
this.  No luck.  Had storage failures earlier, even with corrupted OS
files, but this PostgreSQL cluster w/ Patroni was able to come up each time
without any recovery effort on my part.


>
> When you refer to history files below are you talking about WAL files or
> something else?
>
> Is this:
>
> "recovery command file "recovery.conf" specified neither
> primary_conninfo nor restore_command"
>
> true?
>

True. recovery.conf is controlled by Patroni.  Contents of this file
remained the same for all the cluster nodes with the exception of the
primary_slot_name:

[root@psql01 postgresql-patroni-etcd]# cat recovery.conf
primary_slot_name = 'postgresql0'
standby_mode = 'on'
recovery_target_timeline = 'latest'
[root@psql01 postgresql-patroni-etcd]#

[root@psql02 postgres-backup]# cat recovery.conf
primary_slot_name = 'postgresql1'
standby_mode = 'on'
recovery_target_timeline = 'latest'
[root@psql02 postgres-backup]#

[root@psql03 postgresql-patroni-backup]# cat recovery.conf
primary_slot_name = 'postgresql2'
standby_mode = 'on'
recovery_target_timeline = 'latest'
[root@psql03 postgresql-patroni-backup]#

I've made a copy of the root postgres directory over to another location so
when troubleshooting, I can always revert to the first state the cluster
was in when it failed.

Thx,
TK


>
>
>
> >
> > |-bash-4.2$/usr/pgsql-10/bin/postgres -D /data/patroni
> > --config-file=/data/patroni/postgresql.conf2019-05-2308:26:34.561EDT
> > [10101]LOG:listening onIPv4 address "192.168.0.108",port
> > 54322019-05-2308:26:34.565EDT [10101]LOG:listening onUnix socket
> > "./.s.PGSQL.5432"2019-05-2308:26:34.591EDT [10101]LOG:redirecting log
> > output tologging collector process 2019-05-2308:26:34.591EDT
> > [10101]HINT:Future log output will appear indirectory
> > "log".^C-bash-4.2/usr/pgsql-10/bin/postgres -D /data/patroni
> > --config-file=/data/patroni/postgresql.conf --hot_standby=on
> > --listen_addresses=192.168.0.108 --max_worker_processes=8
> > --max_locks_per_transaction=64 --wal_level=replica
> > --cluster_name=postgres --wal_log_hints=on --max_wal_senders=10
> > --track_commit_timestamp=off --max_prepared_transactions=0 --port=5432
> > --max_replication_slots=10
> > --max_connections=1002019-05-2308:26:45.707EDT [10105]LOG:listening
> > onIPv4 address "192.168.0.108",port 54322019-05-2308:26:45.715EDT
> > [10105]LOG:listening onUnix socket
> > "./.s.PGSQL.5432"2019-05-2308:26:45.740EDT [10105]LOG:redirecting log
> > output tologging collector process 2019-05-2308:26:45.740EDT
> > [10105]HINT:Future log output will appear indirectory
> > "log".^C-bash-4.2$/usr/pgsql-10/bin/postgres -D /data/patroni
> > --config-file=/data/patroni/postgresql.conf --hot_standby=on
> > --listen_addresses=192.168.0.1 --max_worker_processes=8
> > --max_locks_per_transaction=64 --wal_level=replica
> > --cluster_name=postgres --wal_log_hints=on --max_wal_senders=10
> > --track_commit_timestamp=off --max_prepared_transactions=0 --port=5432
> > --max_replication_slots=10
> > --max_connections=1002019-05-2308:29:36.014EDT [10188]LOG:listening
> > onIPv4 address "192.168.0.108",port 54322019-05-2308:29:36.017EDT
> > [10188]LOG:listening onUnix socket
> > "./.s.PGSQL.5432"2019-05-2308:29:36.040EDT [10188]LOG:redirecting log
> > output tologging collector process 2019-05-2308:29:36.040EDT
> > [10188]HINT:Future log output will appear indirectory "log".|
> >
> > The database logs only print out this:
> >
> > |2019-05-2308:25:44.476EDT [10088]HINT:The databaseserver will regularly
> > poll the pg_wal subdirectory tocheckforfiles placed
> > there.2019-05-2308:25:44.477EDT [10088]LOG:entering standby mode
> > 2019-05-2308:25:50.209EDT [10086]LOG:received fast shutdownrequest
> > 2019-05-2308:25:50.215EDT [10089]LOG:shutting down
> > 2019-05-2308:25:50.221EDT [10086]LOG:databasesystem isshut down
> > 2019-05-2308:26:11.560EDT [10094]LOG:databasesystem was shut down
> > inrecovery at 2019-05-2308:25:50ED