Thread: 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 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.
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.klaver@aklaver.com
Adrian Klaver <adrian.klaver@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).
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
On 5/21/20 3:47 PM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@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. > > 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.klaver@aklaver.com
On Thu, May 21, 2020 at 3:57 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 5/21/20 3:47 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@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.
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.
On 5/21/20 4:06 PM, David G. Johnston wrote: > On Thu, May 21, 2020 at 3:57 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 5/21/20 3:47 PM, Tom Lane wrote: > > Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, May 21, 2020 at 3:57 PM Adrian Klaver <adrian.klaver@aklaver.com>
> 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
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.
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.
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.
On Thu, May 21, 2020 at 10:41 PM Andrus <kobruleht2@hot.ee> 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.
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.
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.
"Andrus" <kobruleht2@hot.ee> 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
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.
"Andrus" <kobruleht2@hot.ee> 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
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.
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
Attachment
On Sun, May 24, 2020 at 4:10 PM Michael Paquier <michael@paquier.xyz> 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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sun, May 24, 2020 at 4:10 PM Michael Paquier <michael@paquier.xyz> 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
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 replicationplatforms 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.
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 addedto database they will start replicate automatically ? Will it require more powerful backup server to replay main server sql stream from different databases. Andrus.
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
Attachment
On Sunday, May 24, 2020, Andrus <kobruleht2@hot.ee> wrote:
Hi!Backup in created in Windows from Linux server using pg_receivewal and pg_basebackup .No. Physical copies need to be based on the same platform. If you
Can this backup used for PITR in Linux ?
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.
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.