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

On Sat, Jun 1, 2019 at 8:53 PM Adrian Klaver wrote: > On 6/1/19 5:32 PM, Tom K wrote: > > > > > > > Trying what we did above but on the second node: > > > > Was this node the primary? > > To me the below looks like there are replication slots set up that are > failing. Not sure how to deal with t

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

On 6/1/19 5:32 PM, Tom K wrote: Trying what we did above but on the second node: Was this node the primary? To me the below looks like there are replication slots set up that are failing. Not sure how to deal with this at the moment. You might try single-user mode: https://www.postgres

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

That file just generates the postgres configs. Here is what is generated: -bash-4.2$ cat postgresql.conf # Do not edit this file manually! # It will be overwritten by Patroni! include 'postgresql.base.conf' cluster_name = 'postgres' hot_standby = 'on' listen_addresses = '192.168.0.124' max_conn

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

On 6/1/19 5:21 PM, Tom K wrote: On Sat, Jun 1, 2019 at 7:34 PM Adrian Klaver > wrote: On 6/1/19 4:22 PM, Tom K wrote: > > > > Looks like this crash was far more catastrophic then I thought. By the > looks of things, thinkin

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

On Sat, Jun 1, 2019 at 8:25 PM Tom K wrote: > So the best bet will be trying to get through this error then: > > [ PSQL02 ] > PANIC:replication check point has wrong magic 0 instead of 307747550 > > > > > On Sat, Jun 1, 2019 at 8:21 PM Tom K wrote: > >> >> >> On Sat, Jun 1, 2019 at 7:34 PM Adri

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

So the best bet will be trying to get through this error then: [ PSQL02 ] PANIC:replication check point has wrong magic 0 instead of 307747550 On Sat, Jun 1, 2019 at 8:21 PM Tom K wrote: > > > On Sat, Jun 1, 2019 at 7:34 PM Adrian Klaver > wrote: > >> On 6/1/19 4:22 PM, Tom K wrote: >> > >

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

On Sat, Jun 1, 2019 at 7:34 PM Adrian Klaver wrote: > On 6/1/19 4:22 PM, Tom K wrote: > > > > > > > > > Looks like this crash was far more catastrophic then I thought. By the > > looks of things, thinking on psql02 would be my best bet. > > > > The more I look at it the more I think the replicat

Re: Questions about btree_gin vs btree_gist for low cardinality columns

>From what Peter showed, the answer to (part of) the original questions seems to be that *yes*, a B-tree GIN can be quite appealing. The test times aren't too worrisome, and the index size is about 1/12th of a B-tree. I added on the sizes, and divided each index size by a full B-tree: Method

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

On 6/1/19 4:22 PM, Tom K wrote: Looks like this crash was far more catastrophic then I thought.  By the looks of things, thinking on psql02 would be my best bet. The more I look at it the more I think the replication was not doing what you thought it was doing. That psql02 was the pri

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

On 6/1/19 4:22 PM, Tom K wrote: I thought you said you had copied in data directories from the other nodes, did I remember correctly? Yep, you remembered correctly. I copied the files as they were, out to a temporary folder under root for each node but never dug into base/ etc an

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

On Sat, Jun 1, 2019 at 7:12 PM Adrian Klaver wrote: > On 6/1/19 3:56 PM, Tom K wrote: > > > > > > > > > postgres=# select oid, datname from pg_database ; > >oid | datname > > ---+--- > > 13806 | postgres > > 1 | template1 > > 13805 | template0 > > (3 rows) > > > > So t

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

On 6/1/19 3:56 PM, Tom K wrote: postgres=# select oid, datname from pg_database ;   oid  |  datname ---+---  13806 | postgres      1 | template1  13805 | template0 (3 rows) So there are only the system databases available -bash-4.2$ cd /data/patroni/ -bash-4.2$ ls -altr

Re: Questions about btree_gin vs btree_gist for low cardinality columns

Peter, thanks a lot for picking up on what I started, improving it, and reporting back. I *thought *I was providing timing estimates from the EXPLAIN cost dumps. Seems not. Well, there's another thing that I've learned. Your explanation of why the hash index bloats out makes complete sense, I ough

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

On Sat, Jun 1, 2019 at 6:36 PM Adrian Klaver wrote: > On 6/1/19 3:14 PM, Tom K wrote: > > > > > > > ** Correction. There is postgres, template1 and template2 but none of > > the other databases we had. > > In a psql session do: > > select oid, datname from pg_database ; > > Then go to /data/patr

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

On Sat, Jun 1, 2019 at 6:39 PM Adrian Klaver wrote: > On 6/1/19 3:14 PM, Tom K wrote: > > > > > ** Correction. There is postgres, template1 and template2 but none of > > the other databases we had. > > Just noticed, is that really template2 or is it actually template0? Apologies. You're right

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

On 6/1/19 3:14 PM, Tom K wrote: ** Correction.  There is postgres, template1 and template2 but none of the other databases we had. Just noticed, is that really template2 or is it actually template0? -- Adrian Klaver adrian.kla...@aklaver.com

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

On 6/1/19 3:14 PM, Tom K wrote: ** Correction.  There is postgres, template1 and template2 but none of the other databases we had. In a psql session do: select oid, datname from pg_database ; Then go to /data/patroni an drill down to the base directory. In that directory there should be

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

On Sat, Jun 1, 2019 at 6:05 PM Tom K wrote: > > > On Sat, Jun 1, 2019 at 5:51 PM Adrian Klaver > wrote: > >> On 6/1/19 2:31 PM, Tom K wrote: >> > >> > >> >> > >> > Spoke too soon. There's no tables when it's started without the >> > recovery.conf file. >> >> Where there any errors in the start

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

On Sat, Jun 1, 2019 at 5:51 PM Adrian Klaver wrote: > On 6/1/19 2:31 PM, Tom K wrote: > > > > > > > > > Spoke too soon. There's no tables when it's started without the > > recovery.conf file. > > Where there any errors in the start up? > Nothing I would discern as a startup error: [root@psql03

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

On 6/1/19 2:31 PM, Tom K wrote: Spoke too soon.  There's no tables when it's started without the recovery.conf file. Where there any errors in the start up? Are there databases in the clusters system(template1, postgres, etc) or user? Did you start against the correct PG_DATA directo

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

On 6/1/19 2:30 PM, Tom K wrote: On Sat, Jun 1, 2019 at 4:52 PM Adrian Klaver > wrote: On 6/1/19 12:42 PM, Tom K wrote: > > > > Of note are the characters f2W below.  I see nothing in the postgres > source code to indicate this is

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

On Sat, Jun 1, 2019 at 5:30 PM Tom K wrote: > > > On Sat, Jun 1, 2019 at 4:52 PM Adrian Klaver > wrote: > >> On 6/1/19 12:42 PM, Tom K wrote: >> > >> > >> >> > >> > Of note are the characters f2W below. I see nothing in the postgres >> > source code to indicate this is any recognizable postgres

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

On Sat, Jun 1, 2019 at 4:52 PM Adrian Klaver wrote: > On 6/1/19 12:42 PM, Tom K wrote: > > > > > > > > > Of note are the characters f2W below. I see nothing in the postgres > > source code to indicate this is any recognizable postgres message. A > > part of me suspects that the postgres binarie

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

On 6/1/19 2:08 PM, Tom K wrote: Yep, cheap LAB hardware with no power redundancy ( yet ) . I don't suppose a pg_dump was done anytime recently? -- Adrian Klaver adrian.kla...@aklaver.com

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

On Sat, Jun 1, 2019 at 4:11 PM Adrian Klaver wrote: > On 6/1/19 12:32 PM, Tom K wrote: > > > > > > > > > What if you move the recovery.conf file out? > > > > > > Will try. > > > > > > > > The below looks like missing/corrupted/incorrect files. Hard to tell > > without knowing what Pat

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

On 6/1/19 12:42 PM, Tom K wrote: Of note are the characters f2W below.  I see nothing in the postgres source code to indicate this is any recognizable postgres message.  A part of me suspects that the postgres binaries got corrupted.   Had this case occur with glib-common and a reinstall

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

On Sat, Jun 1, 2019 at 3:32 PM Tom K wrote: > > > On Sat, Jun 1, 2019 at 9:55 AM Adrian Klaver > wrote: > >> On 5/31/19 7:53 PM, Tom K wrote: >> > >> >> > There are two places to connect with the Patroni community: on >> github, >> > via Issues and PRs, and on channel #patroni in the Pos

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

On 6/1/19 12:32 PM, Tom K wrote: What if you move the recovery.conf file out? Will try. The below looks like missing/corrupted/incorrect files. Hard to tell without knowing what Patroni did? Storage disappeared from underneath these clusters.  The OS was of course still

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

On Sat, Jun 1, 2019 at 9:55 AM Adrian Klaver wrote: > On 5/31/19 7:53 PM, Tom K wrote: > > > > > There are two places to connect with the Patroni community: on > github, > > via Issues and PRs, and on channel #patroni in the PostgreSQL Slack. > If > > you're using Patroni, or just int

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

On 5/31/19 7:53 PM, Tom K wrote: There are two places to connect with the Patroni community: on github, via Issues and PRs, and on channel #patroni in the PostgreSQL Slack. If you're using Patroni, or just interested, please join us. Will post there as well.  Thank you.  My thin

Re: Questions about btree_gin vs btree_gist for low cardinality columns

On 2019-06-01 17:44:00 +1000, Morris de Oryx wrote: > Since I've been wondering about this subject, I figured I'd take a bit of time > and try to do some tests. I'm not new to databases or coding, but have been > using Postgres for less than two years. I haven't tried to generate large > blocks of

Re: Questions about btree_gin vs btree_gist for low cardinality columns

On Sat, Jun 1, 2019 at 6:24 PM Gavin Flower wrote: > On 01/06/2019 14:52, Morris de Oryx wrote: > > I'd expect the distribution of values to be closer to a power law than > the Normal distribution -- at very least a few states would have the > most lookups. But this is my gut feel, not based on

Re: Questions about btree_gin vs btree_gist for low cardinality columns

On 01/06/2019 14:52, Morris de Oryx wrote: [...] For an example, imagine an address table with 100M US street addresses with two character state abbreviations. So, say there are around 60 values in there (the USPS is the mail system for a variety of US territories, possessions and friends in th

Re: Questions about btree_gin vs btree_gist for low cardinality columns

Since I've been wondering about this subject, I figured I'd take a bit of time and try to do some tests. I'm not new to databases or coding, but have been using Postgres for less than two years. I haven't tried to generate large blocks of test data directly in Postgres before, so I'm *sure* that th