Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


How to set logical replication for all user databases in cluster so that
when new database is added or new tables are added to database they will
start replicate automatically ?

I think that it would be good if you spend some time reading the
documentation on this stuff, particularly the part about restrictions,
to understand the use cases where that can become useful:
https://www.postgresql.org/docs/devel/logical-replication.html


Thank you.
I read it and havent found any reference to PITR recovery.
For PITR recovery it should probably save sql statements to files and allow to 
specify recovery target time
for applying sql statements to base backup.

Is PITR recovery supported only using binary WAL files ?

Other limits can probably be solved.

Andrus.

Re: Query returns no rows in pg_basebackup cluster

[David G. Johnston]

On Sunday, May 24, 2020, Andrus  wrote:

> Hi!
>
> Backup in created in Windows from Linux server using pg_receivewal and
>>> pg_basebackup .
>>> Can this backup used for PITR in Linux ?
>>>
>> No.  Physical copies need to be based on the same platform.  If you
>> wish to replicate a cluster without any platform, architecture or even
>> not-too-many major version constraints, there is also logical
>> replication available since v10.
>>
>
> Will logical replication also allow two modes:
>  1. PITR recovery can used if needed
>  2. Hot standby: User databases in both clusters contain same data.
>
>
Why are you spending so much effort on this Window/Linux hybrid setup?  Get
yourself another Linux server and setup physical replication.  It sounds
like it will exactly meet your requirements and you will waste more time
and money working out alternatives than the server would cost.

David J.

Re: Query returns no rows in pg_basebackup cluster

[Michael Paquier]

On Mon, May 25, 2020 at 09:02:49AM +0300, Andrus wrote:
> Will logical replication also allow two modes:
>  1. PITR recovery can used if needed
>  2. Hot standby: User databases in both clusters contain same data.
> 
> How to set logical replication for all user databases in cluster so that
> when new database is added or new tables are added to database they will
> start replicate automatically ?

I think that it would be good if you spend some time reading the
documentation on this stuff, particularly the part about restrictions,
to understand the use cases where that can become useful:
https://www.postgresql.org/docs/devel/logical-replication.html
--
Michael


signature.asc
Description: PGP signature

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


No.  Physical copies need to be based on the same platform.

Does the O/S that the client software runs on really affect this?

To the extent that the O/S determines text sort order, yes; see thread.
The short answer here is that we aren't going to support such cases.
If you try to replicate across platforms, and it works, you're in luck.
If it doesn't work, you get to keep both pieces; we will not accept
that as a bug.


In 2017 Peter wrote that  ICU-based collations will offered alongside the 
libc-based collations (1)
Currently it still requires re-compilation of Postgres for all binary 
replication platforms.
Maybe ICU locale will selected during installation automatically in Postgres 13 . Using same ICU locale in all replication platforms 
will hopefully fix the issue.


Currently option is to use ucs_basic as default collation when creating cluster.

(1) https://www.2ndquadrant.com/en/blog/icu-support-postgresql-10/

Andrus. 

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


Backup in created in Windows from Linux server using pg_receivewal and 
pg_basebackup .
Can this backup used for PITR in Linux ?

No.  Physical copies need to be based on the same platform.  If you
wish to replicate a cluster without any platform, architecture or even
not-too-many major version constraints, there is also logical
replication available since v10.


Will logical replication also allow two modes:
 1. PITR recovery can used if needed
 2. Hot standby: User databases in both clusters contain same data.

How to set logical replication for all user databases in cluster so that when new database is added or new tables are added to 
database they will start replicate automatically ?

Will it require more powerful backup server to replay main server sql stream 
from different databases.

Andrus. 

Re: Query returns no rows in pg_basebackup cluster

[Tom Lane]

"David G. Johnston"  writes:
> On Sun, May 24, 2020 at 4:10 PM Michael Paquier  wrote:
>> No.  Physical copies need to be based on the same platform.

> Does the O/S that the client software runs on really affect this?

To the extent that the O/S determines text sort order, yes; see thread.

The short answer here is that we aren't going to support such cases.
If you try to replicate across platforms, and it works, you're in luck.
If it doesn't work, you get to keep both pieces; we will not accept
that as a bug.

regards, tom lane

Re: Query returns no rows in pg_basebackup cluster

[David G. Johnston]

On Sun, May 24, 2020 at 4:10 PM Michael Paquier  wrote:

> On Fri, May 22, 2020 at 09:53:37AM +0300, Andrus wrote:
> > Backup in created in Windows from Linux server using pg_receivewal and
> pg_basebackup .
> > Can this backup used for PITR in Linux ?
>
> No.  Physical copies need to be based on the same platform.  If you
> wish to replicate a cluster without any platform, architecture or even
> not-too-many major version constraints, there is also logical
> replication available since v10.
>

Does the O/S that the client software runs on really affect this?  I would
expect that you could store the offline files anywhere.  As long as the
architecture your original server is on and the one you are restoring to
are the same the restored server should work.  They are just bytes until a
server interprets them, no?

David J.

Re: Query returns no rows in pg_basebackup cluster

[Michael Paquier]

On Fri, May 22, 2020 at 09:53:37AM +0300, Andrus wrote:
> Backup in created in Windows from Linux server using pg_receivewal and 
> pg_basebackup .
> Can this backup used for PITR in Linux ?

No.  Physical copies need to be based on the same platform.  If you
wish to replicate a cluster without any platform, architecture or even
not-too-many major version constraints, there is also logical
replication available since v10.
--
Michael


signature.asc
Description: PGP signature

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


ERROR:  item order invariant violated for index "desktop_baas_liigid_idx"
DETAIL:  Lower index tid=(3,15) (points to index tid=(16,4098)) higher index tid=(3,16) (points to index tid=(17,4098)) page 
lsn=292/630C0CE8.

SQL state: XX002

Uh huh ... and I'll bet the same test on the source server is just fine?
I don't find it surprising in the least that different platforms have
different ideas on fine points like how to sort a leading underscore.
Those things just aren't that well standardized.


This column is not used for locale specific data.

Running 

alter table desktop alter  baas type char(8) collate ucs_basic 

fixes the issue. 
Is this fix reasonable ?

What other issues may occur ?

Can base backup created in windows using pg_basecakup used in Linux without 
such fix?

Andrus.

Re: Query returns no rows in pg_basebackup cluster

[Tom Lane]

"Andrus"  writes:
>> Possibly you could try running contrib/amcheck on the index in question
>> and see if it reports any issues.

> I tried and it reports error

> ERROR:  item order invariant violated for index "desktop_baas_liigid_idx"
> DETAIL:  Lower index tid=(3,15) (points to index tid=(16,4098)) higher index 
> tid=(3,16) (points to index tid=(17,4098)) page 
> lsn=292/630C0CE8.
> SQL state: XX002

Uh huh ... and I'll bet the same test on the source server is just fine?

I don't find it surprising in the least that different platforms have
different ideas on fine points like how to sort a leading underscore.
Those things just aren't that well standardized.

regards, tom lane

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


The sorting rules for this locale must be the same in both platforms.
Only locale names are different.

I think they are less alike than you hoped, because if they were alike,
you wouldn't be seeing this problem.
Possibly you could try running contrib/amcheck on the index in question
and see if it reports any issues.


I tried and it reports error

ERROR:  item order invariant violated for index "desktop_baas_liigid_idx"
DETAIL:  Lower index tid=(3,15) (points to index tid=(16,4098)) higher index tid=(3,16) (points to index tid=(17,4098)) page 
lsn=292/630C0CE8.

SQL state: XX002

Andrus.

Re: Query returns no rows in pg_basebackup cluster

[Tom Lane]

"Andrus"  writes:
>> 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.

I think they are less alike than you hoped, because if they were alike,
you wouldn't be seeing this problem.

Possibly you could try running contrib/amcheck on the index in question
and see if it reports any issues.

regards, tom lane

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


Database in Windows is in read-only (recovery) mode so it cannot changed.

Then you might as well just rm -rf it (or whatever the equivalent Windows
incantation is).  On Windows, that database is broken and useless.


Backup in created in Windows from Linux server using pg_receivewal and 
pg_basebackup .
Can this backup used for PITR in Linux ?

Andrus.

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


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 false
negative – assume >something will break or simply give incorrect results.


This base backup should used for recovery. Taking new base backup in Linux does 
not allow to recover to earlier date.
Both servers have Intel 64 bit CPUs.
I understand that only issue is the index structure and that REINDEX will fix 
this.
What other issues may occur ?

Will pg_dump/pg_restore in Windows server fix all issues.

Andrus. 

Re: Query returns no rows in pg_basebackup cluster

[David G. Johnston]

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 false negative - assume something
will break or simply give incorrect results.

David J.

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

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 Linux locale name.


Which means all his text indexes are corrupt according to the
destination platform's sorting rules, which easily explains the
observed misbehavior (ie, index searches not finding the expected rows).


Lot of queries seems working properly.

REINDEX would fix it. 


REINDEX throws error

ERROR:  cannot execute REINDEX during recovery
SQL state: 25006


But the major point here is you can't just ignore
a collation mismatch, which in turn implies that you can't do physical
replication from Linux to Windows, or vice versa (and most other
cross-platform cases are just as dangerous).


Database is used in recovery mode to find proper recovery point and to get data 
from it in this point.
Locales are actually same. In windows Postgres does not recognize Linux locale 
name.


Database in Windows is in read-only (recovery) mode so it cannot changed.

Then you might as well just rm -rf it (or whatever the equivalent Windows
incantation is).  On Windows, that database is broken and useless.


Most queries seems to work. 
Database should examined to get accidently deleted data from it.


Is making it read-write and index only solution or can it fixed in read-only 
database also, e-q forcing same local in postgres.conf

Andrus.

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

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 resorts to a seqscan --- which will work fine.
It's just index searches (and index-based sorts) that are broken.
Of course, if there isn't an index on the column in question
then this theory falls to the ground.


There is composite index on baas column

CREATE TABLE public.desktop
(
   id integer NOT NULL DEFAULT nextval('desktop_id_seq'::regclass),
   recordtype character(5) COLLATE pg_catalog."default" NOT NULL,
   klass character(1) COLLATE pg_catalog."default",
   baas character(8) COLLATE pg_catalog."default" NOT NULL,
   liigid character(1) COLLATE pg_catalog."default" NOT NULL DEFAULT ''::bpchar,
   jrk numeric(4,0) NOT NULL DEFAULT 0,
...
   CONSTRAINT desktop_pkey PRIMARY KEY (id),
   CONSTRAINT desktop_baas_not_empty CHECK (baas <> ''::bpchar),
   CONSTRAINT desktop_id_check CHECK (id > 0),
   CONSTRAINT desktop_recordtype_check CHECK (recordtype = 'Aken'::bpchar OR 
recordtype = 'Veerg'::bpchar)
)

TABLESPACE pg_default;
CREATE INDEX desktop_baas_liigid_idx
   ON public.desktop USING btree
   (baas COLLATE pg_catalog."default" ASC NULLS LAST, liigid COLLATE 
pg_catalog."default" ASC NULLS LAST)
   TABLESPACE pg_default;

Maybe it is possible to force postgres in windows to use the same locale as in 
Linux. Locales are actually the same.

Andrus.

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

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: Query returns no rows in pg_basebackup cluster

[Tom Lane]

"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 properly 16 rows. "

> 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 resorts to a seqscan --- which will work fine.
It's just index searches (and index-based sorts) that are broken.

Of course, if there isn't an index on the column in question
then this theory falls to the ground.

regards, tom lane

Re: Query returns no rows in pg_basebackup cluster

[Adrian Klaver]

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 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 platform with completely different sorting rules.
 > Which means all his text indexes are corrupt according to the
 > destination platform's sorting rules, which easily explains the
 > observed misbehavior (ie, index searches not finding the expected
rows).

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 properly 16 rows. "

My suspicion is that first case is for the replicated database and
failed for the reasons you mentioned and that the second case is for a
'native' Windows instance. Just trying to get confirmation.


Nothing in the OP's text suggests a different server is involved - 
rather same server but LIKE vs equals.


Aah, missed that.



The LIKE query probably doesn't use an index and thus finds the relevant 
data via sequential scan and equality checks on each record.


David J.




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

Re: Query returns no rows in pg_basebackup cluster

[David G. Johnston]

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 Postgres on Windows?
> >
> > No, what it sounds like is the OP tried to physically replicate a
> > database on another platform with completely different sorting rules.
> > Which means all his text indexes are corrupt according to the
> > destination platform's sorting rules, which easily explains the
> > observed misbehavior (ie, index searches not finding the expected rows).
>
> 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 properly 16 rows. "
>
> My suspicion is that first case is for the replicated database and
> failed for the reasons you mentioned and that the second case is for a
> 'native' Windows instance. Just trying to get confirmation.
>

Nothing in the OP's text suggests a different server is involved - rather
same server but LIKE vs equals.

The LIKE query probably doesn't use an index and thus finds the relevant
data via sequential scan and equality checks on each record.

David J.

Re: Query returns no rows in pg_basebackup cluster

[Adrian Klaver]

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 physically replicate a
database on another platform with completely different sorting rules.
Which means all his text indexes are corrupt according to the
destination platform's sorting rules, which easily explains the
observed misbehavior (ie, index searches not finding the expected rows).


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 properly 16 rows. "

My suspicion is that first case is for the replicated database and 
failed for the reasons you mentioned and that the second case is for a 
'native' Windows instance. Just trying to get confirmation.




REINDEX would fix it.  But the major point here is you can't just ignore
a collation mismatch, which in turn implies that you can't do physical
replication from Linux to Windows, or vice versa (and most other
cross-platform cases are just as dangerous).


Database in Windows is in read-only (recovery) mode so it cannot changed.


Then you might as well just rm -rf it (or whatever the equivalent Windows
incantation is).  On Windows, that database is broken and useless.

regards, tom lane




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

Re: Query returns no rows in pg_basebackup cluster

[Tom Lane]

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 platform with completely different sorting rules.
Which means all his text indexes are corrupt according to the
destination platform's sorting rules, which easily explains the
observed misbehavior (ie, index searches not finding the expected rows).

REINDEX would fix it.  But the major point here is you can't just ignore
a collation mismatch, which in turn implies that you can't do physical
replication from Linux to Windows, or vice versa (and most other
cross-platform cases are just as dangerous).

>> Database in Windows is in read-only (recovery) mode so it cannot changed.

Then you might as well just rm -rf it (or whatever the equivalent Windows
incantation is).  On Windows, that database is broken and useless.

regards, tom lane

Re: Query returns no rows in pg_basebackup cluster

[Adrian Klaver]

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 Windows server same query using like

select *  from firma1.desktop where baas like '_LOGIFAI'

returns properly 16 rows.


Are you referring to two different instances of Postgres on Windows?



Maybe this is because database locale is not known in windows:

CREATE DATABASE sba
    WITH    OWNER = sba_owner
    ENCODING = 'UTF8'
    LC_COLLATE = 'et_EE.UTF-8'
    LC_CTYPE = 'et_EE.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Correct encoding for windows should be

LC_COLLATE = 'Estonian_Estonia.1257'
LC_CTYPE = 'Estonian_Estonia.1257'

IF so how to to fix windows cluster so that query returns proper result 
in windows also?

Database in Windows is in read-only (recovery) mode so it cannot changed.
Postgres 12 is used.

Andrus.





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