Re: Query returns no rows in pg_basebackup cluster

On Thu, May 21, 2020 at 10:41 PM Andrus wrote: > Main server is in Linux and backup server is in windows. > This is not a supported setup if you want to run a physical backup. Your backup and your primary need to be the same - software and hardware. Consider anything that is working to be a

Re: Query returns no rows in pg_basebackup cluster

Hi! No, what it sounds like is the OP tried to physically replicate a database on another platform with completely different sorting rules. The sorting rules for this locale must be the same in both platforms. Only locale names are different. It looks like windows server does not recognize

Re: Query returns no rows in pg_basebackup cluster

Hi! The LIKE query probably doesn't use an index and thus finds the relevant data via sequential scan and equality checks on each record. Yeah, exactly. An equality condition will use a btree index if available. LIKE, however, sees the "_" as a wildcard so it cannot use an index and

Re: Query returns no rows in pg_basebackup cluster

Hi! Are you referring to two different instances of Postgres on Windows? No. Main server is in Linux and backup server is in windows. Andrus.

Re: Help understanding SIReadLock growing without bound on completed transaction

On Thu, May 21, 2020 at 5:19 PM, Thomas Munro < thomas.mu...@gmail.com > wrote: > > > > On Fri, May 22, 2020 at 7:48 AM Mike Klaas < mike@ superhuman. com ( > m...@superhuman.com ) > wrote: > > > >> >> >> pid:2263461 >> >> >> > > > > That's an unusually high looking pid. Is that

Re: Help understanding SIReadLock growing without bound on completed transaction

On Fri, May 22, 2020 at 7:48 AM Mike Klaas wrote: > locktype: page > relation::regclass::text: _pkey > virtualtransaction: 36/296299968 > granted:t > pid:2263461 That's an unusually high looking pid. Is that expected, for example did you crank Linux's pid_max right up, or is this AIX, or

Re: Should I use JSON?

stan writes: > Worming on a small project, and have been doing a lot of Perl scripting to > parse various types of files to populate the database. Now I need to get > data from a cloud services provider (time-keeping). They have a REST API > that returns data in a JSOSN format. > > So here is

Re: Query returns no rows in pg_basebackup cluster

"David G. Johnston" writes: > On Thu, May 21, 2020 at 3:57 PM Adrian Klaver > wrote: >> Well what I was trying to figure out was: >> "Windows server this query returns 0 rows. >> In Windows server same query using like >> select * from firma1.desktop where baas like '_LOGIFAI' >> returns

Re: Query returns no rows in pg_basebackup cluster

On 5/21/20 4:06 PM, David G. Johnston wrote: On Thu, May 21, 2020 at 3:57 PM Adrian Klaver > wrote: On 5/21/20 3:47 PM, Tom Lane wrote: > Adrian Klaver mailto:adrian.kla...@aklaver.com>> writes: >> On 5/21/20 1:20 PM, Andrus wrote: >>> In

Re: Query returns no rows in pg_basebackup cluster

On Thu, May 21, 2020 at 3:57 PM Adrian Klaver wrote: > On 5/21/20 3:47 PM, Tom Lane wrote: > > Adrian Klaver writes: > >> On 5/21/20 1:20 PM, Andrus wrote: > >>> In windows pg_basebackup was used to create base backup from Linux > server. > > > >> Are you referring to two different instances of

Re: Query returns no rows in pg_basebackup cluster

On 5/21/20 3:47 PM, Tom Lane wrote: Adrian Klaver writes: On 5/21/20 1:20 PM, Andrus wrote: In windows pg_basebackup was used to create base backup from Linux server. Are you referring to two different instances of Postgres on Windows? No, what it sounds like is the OP tried to

Re: Query returns no rows in pg_basebackup cluster

Adrian Klaver writes: > On 5/21/20 1:20 PM, Andrus wrote: >> In windows pg_basebackup was used to create base backup from Linux server. > Are you referring to two different instances of Postgres on Windows? No, what it sounds like is the OP tried to physically replicate a database on another

Re: Query returns no rows in pg_basebackup cluster

On 5/21/20 1:20 PM, Andrus wrote: Hi! In windows pg_basebackup was used to create base backup from Linux server. baas column data type is character(8) In Linux server  query select *  from firma1.desktop where baas='_LOGIFAI' returns 16 rows. Windows server this query returns 0 rows. In

Re: Table partitioning for cloud service?

On 5/21/20 1:23 PM, Israel Brewster wrote: On May 21, 2020, at 12:12 PM, Sándor Daku > wrote: Hi, On Thu, 21 May 2020 at 18:14, Christopher Browne > wrote: On Thu, 21 May 2020 at 11:53, Israel Brewster

Re: pg_basebackup + incremental base backups

Greetings, * Christopher Pereira (krip...@imatronix.cl) wrote: > On 21-May-20 08:43, Stephen Frost wrote: > >* Christopher Pereira (krip...@imatronix.cl) wrote: > >>Is there some way to rebuild the standby cluster by doing a differential > >>backup of the primary cluster directly? > >We've

Re: Table partitioning for cloud service?

> > The database/schema per tenant solution can be tedious when you want to > modify something on the structure and you have numerous tenants. > Therefore I used the "tables with tenant_id" version in a similar situation > but with a slight twist. One of the biggest issue of this solution is

Re: Strategy for materialisation and centralisation of data

On 21/05/20, Michael Stephenson (domehead...@gmail.com) wrote: > On Thu, May 21, 2020 at 10:36 AM Rory Campbell-Lange wrote > > Presently I've been thinking of using triggers or materialized views in > > each database to materialise data into a "matview" schema which is then > > shipped via

Re: Strategy for materialisation and centralisation of data

You might find Materialize interesting: https://materialize.io/ https://youtu.be/zWSdkGq1XWk On Thu, May 21, 2020 at 10:36 AM Rory Campbell-Lange < r...@campbell-lange.net> wrote: > We have quite a few databases of type a and many of type b in a cluster. > Both a and b types are fairly

RE: Should I use JSON?

We make lots of use JSON – but in specific contexts. If we need to pull data out for listing view – always raw fields. If these are detail view only and we need dynamic content depending on record types, JSON is a life saver Z From: pabloa98 Sent: 21 May 2020 20:28 Cc:

Re: Strategy for materialisation and centralisation of data

On Thu, May 21, 2020 at 09:52:02PM +0100, Rory Campbell-Lange wrote: > > Might postgres_fdw help in any way ? > > Thanks for the suggestion. As I noted we are already using pl/proxy and > it works well, although we are soaking up a lot of connections with it. > >From my reading of the

Re: Strategy for materialisation and centralisation of data

On 21/05/20, Karsten Hilbert (karsten.hilb...@gmx.net) wrote: > On Thu, May 21, 2020 at 03:35:59PM +0100, Rory Campbell-Lange wrote: > > > We have quite a few databases of type a and many of type b in a cluster. > > Both a and b types are fairly complex and are different solutions to a > >

Re: Table partitioning for cloud service?

On May 21, 2020, at 12:12 PM, Sándor Daku wrote: > Hi, > > On Thu, 21 May 2020 at 18:14, Christopher Browne > wrote: > On Thu, 21 May 2020 at 11:53, Israel Brewster > wrote: >> > > - Table-based tenancy (e.g. - each table has a

Query returns no rows in pg_basebackup cluster

Hi! In windows pg_basebackup was used to create base backup from Linux server. baas column data type is character(8) In Linux server query select * from firma1.desktop where baas='_LOGIFAI' returns 16 rows. Windows server this query returns 0 rows. In Windows server same query using

Re: Table partitioning for cloud service?

Hi, On Thu, 21 May 2020 at 18:14, Christopher Browne wrote: > On Thu, 21 May 2020 at 11:53, Israel Brewster > wrote: > >> >> - Table-based tenancy (e.g. - each table has a "tenant_id" and queries > need to specify the tenant) > The database/schema per tenant solution can be tedious when you

Help understanding SIReadLock growing without bound on completed transaction

We recently experienced an issue where a transaction that was finished and no longer existed kept slowly accumulating SIReadLocks over the period of a week.  The only way we could remove the locks was by restarting postgresql. The entries in pg_locks resembled: mode: SIReadLock locktype: page

Re: Strategy for materialisation and centralisation of data

On Thu, May 21, 2020 at 03:35:59PM +0100, Rory Campbell-Lange wrote: > We have quite a few databases of type a and many of type b in a cluster. > Both a and b types are fairly complex and are different solutions to a > similar problem domain. All the databases are very read-centric, and all >

Re: Should I use JSON?

On Thu, May 21, 2020 at 8:37 AM stan wrote: > Worming on a small project, and have been doing a lot of Perl scripting to > parse various types of files to populate the database. Now I need to get > data from a cloud services provider (time-keeping). They have a REST API > that returns data in a

Re: Table partitioning for cloud service?

On Thu, May 21, 2020 at 11:41 AM Adam Brusselback wrote: > As an optimization I just worked on for my database earlier this week, I > decided to logically replicate that table from my main authentication > database into a each cluster, and I replaced all references to the FDW for > read-only

Re: Table partitioning for cloud service?

> An interesting answer, if there needs to be shared data, is for the shared data to go in its own database, and use a Foreign Data Wrapper to have each tenants' database access it < https://www.postgresql.org/docs/12/postgres-fdw.html> For my application I went the schema-per-tenant route, but

Re: Table partitioning for cloud service?

> On May 21, 2020, at 7:57 AM, Adrian Klaver wrote: > > On 5/21/20 8:53 AM, Israel Brewster wrote: >>> On May 21, 2020, at 7:36 AM, Adrian Klaver >> > wrote: >>> >>> On 5/21/20 8:29 AM, Israel Brewster wrote: I’m working on my first cloud service, which

Re: Table partitioning for cloud service?

On Thu, 21 May 2020 at 11:53, Israel Brewster wrote: > On May 21, 2020, at 7:36 AM, Adrian Klaver > wrote: > > On 5/21/20 8:29 AM, Israel Brewster wrote: > > I’m working on my first cloud service, which will be backed by a > postgresql database. Currently I only have a single customer, but of

Re: Table partitioning for cloud service?

On 5/21/20 8:53 AM, Israel Brewster wrote: On May 21, 2020, at 7:36 AM, Adrian Klaver > wrote: On 5/21/20 8:29 AM, Israel Brewster wrote: I’m working on my first cloud service, which will be backed by a postgresql database. Currently I only have a single

Re: Should I use JSON?

On Thu, May 21, 2020 at 8:37 AM stan wrote: > So here is the question, should I just manually parse this data, as I have > been doing to insert into appropriate entities into the database? Or > should I > insert the JSON data, and use some queries in the database to populate my > tables from the

Re: Table partitioning for cloud service?

> On May 21, 2020, at 7:36 AM, Adrian Klaver wrote: > > On 5/21/20 8:29 AM, Israel Brewster wrote: >> I’m working on my first cloud service, which will be backed by a postgresql >> database. Currently I only have a single customer, but of course I want to >> design with the possibility of

Re: Should I use JSON?

On 5/21/20 8:37 AM, stan wrote: Worming on a small project, and have been doing a lot of Perl scripting to parse various types of files to populate the database. Now I need to get data from a cloud services provider (time-keeping). They have a REST API that returns data in a JSOSN format. So

Should I use JSON?

Worming on a small project, and have been doing a lot of Perl scripting to parse various types of files to populate the database. Now I need to get data from a cloud services provider (time-keeping). They have a REST API that returns data in a JSOSN format. So here is the question, should I just

Re: Table partitioning for cloud service?

On 5/21/20 8:29 AM, Israel Brewster wrote: I’m working on my first cloud service, which will be backed by a postgresql database. Currently I only have a single customer, but of course I want to design with the possibility of multiple customers in mind. In that vein, I’m wondering what is

Table partitioning for cloud service?

I’m working on my first cloud service, which will be backed by a postgresql database. Currently I only have a single customer, but of course I want to design with the possibility of multiple customers in mind. In that vein, I’m wondering what is “typical” in terms of designing the DB structure

Re: Suggestion on index creation for TEXT data field

Hi David, Adrian, Thanks for the information. Sure, will post on PostGIS community. Regards, PostgAnn. On Thu, May 21, 2020 at 8:21 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, May 21, 2020 at 7:45 AM postgann2020 s > wrote: > >> >And what type of data exactly are we

Re: Suggestion on index creation for TEXT data field

On Thu, May 21, 2020 at 7:45 AM postgann2020 s wrote: > >And what type of data exactly are we talking about. ==> Column is > stroing GIS data. > GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this is maybe better posted to the PostGIS community directly... David J.

Re: Suggestion on table analyze

Hi Adrian, Thanks, I'll check it out. Regards, PostgAnn. On Thu, May 21, 2020 at 8:11 PM Adrian Klaver wrote: > On 5/21/20 7:18 AM, postgann2020 s wrote: > > Hi Team, > > > > Thanks for your support. > > > > Could you please suggest on below query. > > > > Environment > > PostgreSQL: 9.5.15 >

Re: Suggestion on index creation for TEXT data field

On 5/21/20 7:27 AM, postgann2020 s wrote: Hi Team, Thanks for your support. Could you please suggest on below query. Environment PostgreSQL: 9.5.15 Postgis: 2.2.7 Mostly table contains GIS data and we are trying to creating an index on the column which is having an avg width of 149bytes.  

Re: Suggestion on index creation for TEXT data field

Hi David, Thanks for your email. >And what type of data exactly are we talking about. ==> Column is stroing GIS data. Regards, PostgAnn. On Thu, May 21, 2020 at 8:06 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, May 21, 2020 at 7:28 AM postgann2020 s > wrote: > >>

Re: Suggestion on table analyze

On 5/21/20 7:18 AM, postgann2020 s wrote: Hi Team, Thanks for your support. Could you please suggest on below query. Environment PostgreSQL: 9.5.15 Postgis: 2.2.7 Mostly table contain GIS data. While analyzing the table getting below NOTICE. It seems is pretty understanding, but needs help

Re: Suggestion on index creation for TEXT data field

On Thu, May 21, 2020 at 7:28 AM postgann2020 s wrote: > which is having an avg width of 149bytes. > The average is meaningless if your maximum value exceeds a limit. 2. What type of index is the best suited for this type of data?. > And what type of data exactly are we talking about. "TEXT"

Strategy for materialisation and centralisation of data

We have quite a few databases of type a and many of type b in a cluster. Both a and b types are fairly complex and are different solutions to a similar problem domain. All the databases are very read-centric, and all database interaction is currently through plpgsql with no materialised data.

Re: Behaviour of failed Primary

On Thu, May 21, 2020 at 5:38 PM Santhosh Kumar wrote: > > Hi Forum, > If I have a cluster with Synchronous replication enabled with three nodes, > for eg: > > [primary] [hot stand by 1] [host stand by 2] > > And for some unforeseen reasons, if primary fails, the failover will kick in > and hot

Suggestion on index creation for TEXT data field

Hi Team, Thanks for your support. Could you please suggest on below query. Environment PostgreSQL: 9.5.15 Postgis: 2.2.7 Mostly table contains GIS data and we are trying to creating an index on the column which is having an avg width of 149bytes. CREATE INDEX index_idx ON SCHEMA.TABLE

Suggestion on table analyze

Hi Team, Thanks for your support. Could you please suggest on below query. Environment PostgreSQL: 9.5.15 Postgis: 2.2.7 Mostly table contain GIS data. While analyzing the table getting below NOTICE. It seems is pretty understanding, but needs help on the below points. 1 . What might be the

Behaviour of failed Primary

Hi Forum, If I have a cluster with Synchronous replication enabled with three nodes, for eg: [primary] [hot stand by 1] [host stand by 2] And for some unforeseen reasons, if primary fails, the failover will kick in and hot stand by 1 will become new primary and cluster setup will look like

Re: pg_basebackup + incremental base backups

In case of big databases, can we do incremental backups with pg_basebackup? pg_basebackup doesn't support incremental backups, though there's been discussion of adding some kind of support for it, check -hackers if you're curious. Is there any alternative? There's a few different

Re: How to recover from compressed wal archieve in windows

Hi! Will wal_compression=on will produce compressed wal files to additional compression is not needed? Yes. Not sure how it will play with the streaming that pg_receivewal does. I looked into WAL files created with wal_compression=on in pg_wal directory. They still contain lot of ascii data