Query returns no rows in pg_basebackup cluster

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Query returns no rows in pg_basebackup cluster

Andrus Moor
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.

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.


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Tom Lane-2
Adrian Klaver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Adrian Klaver-4
On 5/21/20 3:47 PM, Tom Lane wrote:

> Adrian Klaver <[hidden email]> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

David G Johnston
On Thu, May 21, 2020 at 3:57 PM Adrian Klaver <[hidden email]> wrote:
On 5/21/20 3:47 PM, Tom Lane wrote:
> Adrian Klaver <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Adrian Klaver-4
On 5/21/20 4:06 PM, David G. Johnston wrote:

> On Thu, May 21, 2020 at 3:57 PM Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     On 5/21/20 3:47 PM, Tom Lane wrote:
>      > Adrian Klaver <[hidden email]
>     <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Tom Lane-2
In reply to this post by David G Johnston
"David G. Johnston" <[hidden email]> writes:
> On Thu, May 21, 2020 at 3:57 PM Adrian Klaver <[hidden email]>
> 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


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Andrus Moor
In reply to this post by Adrian Klaver-4
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.


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Andrus Moor
In reply to this post by Tom Lane-2
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.



Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Andrus Moor
In reply to this post by Tom Lane-2
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.


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

David G Johnston
In reply to this post by Andrus Moor
On Thu, May 21, 2020 at 10:41 PM Andrus <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Andrus Moor
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.



Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Andrus Moor
In reply to this post by Tom Lane-2
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.


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Tom Lane-2
In reply to this post by Andrus Moor
"Andrus" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Andrus Moor
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.



Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Tom Lane-2
"Andrus" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Andrus Moor
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.


Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Michael Paquier-2
In reply to this post by Andrus Moor
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 (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

David G Johnston
On Sun, May 24, 2020 at 4:10 PM Michael Paquier <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Query returns no rows in pg_basebackup cluster

Tom Lane-2
"David G. Johnston" <[hidden email]> writes:
> On Sun, May 24, 2020 at 4:10 PM Michael Paquier <[hidden email]> 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


12