BUG #11141: Duplicate primary key values corruption
Hi, PG 9.5.5 on AIX tends to compromise Primary Key and create duplicates when in stress and many concurrent threads updating. Is it BUG #11141 ( Duplicate primary key values corruption ). ? Anyone aware of a resolution for this ? B.T.W - Looks like it happens less on Linux. Thanks Danny
Re: BUG #11141: Duplicate primary key values corruption
9.5.5 is pretty old. I'd strongly think about updating to one of the *seventeen* subsequent patch releases. https://www.postgresql.org/docs/release/9.5.22/ On 6/14/20 2:17 AM, Abraham, Danny wrote: Hi, PG 9.5.5 on AIX tends to compromise Primary Key and create duplicates when in stress and many concurrent threads updating. Is it BUG #11141 ( Duplicate primary key values corruption ). ? Anyone aware of a resolution for this ? B.T.W - Looks like it happens less on Linux. Thanks Danny -- Angular momentum makes the world go 'round.
Re: Something else about Redo Logs disappearing
On Sat, Jun 13, 2020 at 10:13 PM Peter wrote: > On Thu, Jun 11, 2020 at 10:35:13PM +0200, Magnus Hagander wrote: > ! > Okay. So lets behave like professional people and figure how that > ! > can be achieved: > ! > At first, we drop that WAL requirement, because with WAL archiving > ! > it is already guaranteed that an unbroken chain of WAL is always > ! > present in the backup (except when we have a bug like the one that > ! > lead to this discussion). > ! > So this is **not part of the scope**. > ! > > ! > ! I would assume that anybody who deals with backups professionally > wouldn't > ! consider that out of scope, > > I strongly disagree. I might suppose You haven't thought this to the > proper end. See: > You may disagree, but I would argue that this is because you are the one who has not thought it through. But hey, let's agree to disagree. You can see that all the major attributes (scheduling, error-handling, > signalling, ...) of a WAL backup are substantially different to that > of any usual backup. This is a different *Class* of backup object, therefore it needs an > appropriate infrastructure that can handle these attributes correctly. > Yes, this is *exactly* why special-handling the WAL during the base backup makes a lot of sense. Is it required? No. Will it make your backups more reliable? Yes. But it depends on what your priorities are. But, if You never have considered *continuous* archiving, and only > intend to take a functional momentarily backup of a cluster, then You > may well have never noticed these differences. I noticed them mainly > because I did *BUILD* such an infrastructure (the 20 lines of shell > script, you know). > Yes, if you take a simplistic view of your backups, then yes. And yes, I was indeed talking about *professional* approaches. > Sure. ! There is *absolutely* no need for threading to use the current APIs. You > ! need to run one query, go do something else, and then run another > ! query. > > Wrong. The point is, I dont want to "go do something else", I have to > exit() and get back to the initiator at that place. > That is not a requirement of the current PostgreSQL APIs. (in fact, using threading would add a significant extra burden there, as libpq does not allow sharing of connections between threads) That is a requirement, and indeed a pretty sharp limitation, of the *other* APIs you are working with, it sounds like. The PostgreSQL APIs discussed to *not* require you to do an exit(). Nor do they require any form of threading. And the fact that you need to do an exit() would negate any threading anyway, so that seems to be a false argument regardless. This is also clearly visible in Laurenz' code: he utilizes two > unchecked background tasks (processes, in this case) with loose > coupling for the purpose, as it does not work otherwise. > Yes, because he is also trying to work around a severely limited API *on the other side*. There's plenty of backup integrations that don't have this limitation. They all work perfectly fine with no need for exit() and certainly no weird need for special threading. The most interesting point in there appears to be this: > > that the backup label and tablespace map files are not written to > > disk. Instead, their would-be contents are returned in *labelfile > > and *tblspcmapfile, > > This is in do_pg_start_backup() - so we actually HAVE this data > already at the *START* time of the backup! > Then why in hell do we wait until the END of the backup before we > hand this data to the operator: at a time when the DVD with the > Because it cannot be safely written *into the data directory*. Now, it could be written *somewhere else*, that is true. And then you would add an extra step at restore time to rename it back. But then your restore would now also require a plugin. ( > backup is already fixated and cannot be changed anymore, so that > You don't need to change the the backup, only append to it. If you are calling pg_stop_backup() at a time when that is no longer possible, then you are calling pg_stop_backup() at the wrong time. As I can read, there is no difference in the function requirements > between exclusive and non-exclusive mode, in that regard: the > backup-label file is NOT necessary in the running cluster data tree, > BUT it should get into the RESTORED data tree before starting it. > Correct. It is in fact actively harmful in the running cluster data tree. And I can't find a single one of those "big problems". What I do find > is just people whining that their cluster doesn't start and they can't > simply delete a file, even if told so. Like soldier complaining that > his gun doesn't shoot and he has no idea how to reload. > Have you actually tried it? Or dealt with the many people who have run into corruption around this? Again, as suggested before, review the discussions that led up to the changes. There are plenty of examples there. ! > I now hope very much that M
Re: Oracle vs. PostgreSQL - a comment
On Sat, Jun 13, 2020 at 09:27:25PM -0400, Bruce Momjian wrote: > On Sat, Jun 13, 2020 at 05:06:37PM -0500, Ron wrote: > > On 6/13/20 1:46 PM, Bruce Momjian wrote: > > > On Wed, Jun 3, 2020 at 08:53:45PM +0200, Andreas Joseph Krogh wrote: > > > > I agree these are all technical issues, but nevertheless - > > > > "implementation > > > > details", which DBAs don't care about. What's important from a DBA's > > > > perspective is not whether WAL is cluster-wide or database-wide, but > > > > whether > > > > it's possible to manage backups/PITR/restores of individual databases > > > > in a more > > > > convenient matter, which other RDBMS-vendors seem to provide. > > > > I love PG, have been using it professionally since 6.5, and our company > > > > depends > > > > on it, but there are things other RDBMS-vendors do better... > > > The bigger issue is that while we _could_ do this, it would add more > > > problems and complexity, and ultimately, I think would make the > > > software less usable overall and would be a net-negative. We know of no > > > way to do it without a ton of negatives. > > > > How do other RDBMSs do it with ease? (I know it's an architectural issue, > > but what's the architectural issue?) > > I don't know. I don't know the details, but I do know the general issues. Other vendors must have sacrificed architectural simplicity, features, reliability, or performance to allow these things. For example, it wouldn't be hard to just make databases another level of container above schemas to allow for simple cross-database queries, but we would lose the security isolation of databases (connection control. private system tables and extensions) to do that. Having per-database WAL causes loss of performance, reliability issues, and architectural complexity. Those problems might be solvable, but you will need to take a hit in one of these areas. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: BUG #11141: Duplicate primary key values corruption
On 6/14/20 12:17 AM, Abraham, Danny wrote: Hi, PG 9.5.5 on AIX tends to compromise Primary Key and create duplicates when in stress and many concurrent threads updating. Is it BUG #11141 ( Duplicate primary key values corruption ). ? Anyone aware of a resolution for this ? According to the bug report: https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=refs%2Fheads%2FREL9_5_STABLE&st=commit&s=duplicate This was done almost 2 years before 9.5 was released so it would have the fix in it. A quick check of the Git repo showed nothing that seemed to apply since. Then again it was a quick check. I would still take Ron's advice and upgrade to latest minor release. If that does not fix the problem then submit a bug report with a test case. B.T.W - Looks like it happens less on Linux. Thanks Danny -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_service.conf and client support
On 6/13/20 10:03 PM, Niels Jespersen wrote: Hello all I just found out about the pg service file. https://www.postgresql.org/docs/current/libpq-pgservice.html I don’t know why it took me so long finding this. I have been looking for ways to abstract physical details of data location away for clients (much like Oracle’s tnsnames). Want to move a cluster to a new host. Sure, move it, edit pg_service.conf and clients will not know the difference. It works great for at least psql and psycopg2. But I cannot find anything on pg_service.conf and the Postgres ODBC driver and Npgsql for .Net I know pg_service.conf support is implemented through libpq and support for rivers not using libpq is not a given thing. But I think the need for the abstraction of connection details is a general one. Can anyone shed som light on the ubiquitousness of support for pg_service.conf? Are there any other mechanisms with broader support, that can be used instead of pg_service.conf (if support is scarce beyond what builds on libpq)? Only thing I know of is something like pgbouncer(http://www.pgbouncer.org/) where you keep a constant connection setting for the pooler and then change the settings for the database(s) it connects to. Thank you. Regards Niels Jespersen -- Adrian Klaver adrian.kla...@aklaver.com
Unable to execute pg_dump
Hello everyone, I am trying to work out a way to perform a PostgreSQL pg_dump of a single DB via RHEL v6.10 command line. Now the system I am working on is that you have to shell into Bash 4.1 first before you get to PSQL. Now I can access the actual PSQL db using the following syntax from the command line which works fine: '''sudo -u postgres -H --psql -px -d db_name''' If I enter the following syntax from the RHEL command line: '''sudo su postgres''' I end up in the bash-4.1 shell. When executing the following command from within the shell: bash-4.1$ pg_dump db_name > /tmp/my_database.sql I am presented with the following error: pg_dump: [archiver (db)] connection to database "db_name" failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? I am expecting the db to be exported to /tmp/my_database.sql file. I have tried various combinations of commands from both RHEL and bash command lines but cannot achieve the desired outcome. Your assistance would be greatly appreciated, thank you. Regards, Joe28a
Re: Unable to execute pg_dump
On Sunday, June 14, 2020, Joseph Maruca wrote: > > '''sudo -u postgres -H --psql -px -d db_name''' > > If I enter the following syntax from the RHEL command line: > > '''sudo su postgres''' > > I end up in the bash-4.1 shell. When executing the following command from > within the shell: bash-4.1$ pg_dump db_name > /tmp/my_database.sql I am > presented with the following error: > > pg_dump: [archiver (db)] connection to database "db_name" failed: could > not connect to server: No such file or directory Is the server running > locally and accepting connections on Unix domain socket > "/var/run/postgresql/.s.PGSQL.5432"? > > Not a RHEL user but... For psql you specify the port but not for pg_dump. For psql you seem to be switching to the postgres user’s environment but don’t do that for pg_dump. In short it seems like you need to trace down your O/S environment differences when each command is run. David J.
Re: Unable to execute pg_dump
Joseph Maruca writes: > I end up in the bash-4.1 shell. When executing the following command from > within the shell: bash-4.1$ pg_dump db_name > /tmp/my_database.sql I am > presented with the following error: > pg_dump: [archiver (db)] connection to database "db_name" failed: could not > connect to server: No such file or directory Is the server running locally > and accepting connections on Unix domain socket > "/var/run/postgresql/.s.PGSQL.5432"? Ah, this is a symptom we've seen before. There is disagreement about where the postmaster ought to put its socket file --- traditionally PG puts it in /tmp/, but some people feel that's a security hazard and want to put it in a bespoke directory such as /var/run/postgresql/. It appears from these symptoms that (a) you are running a server that puts the socket in /tmp (probably you got that server from a PGDG RPM?) but (b) you are trying to connect to it using a Red-Hat-provided libpq (which defaults to expecting the file to be in /var/run/postgresql/). You have various possible workarounds: * tell the server to create a socket file in /var/run/postgresql/ too (see unix_socket_directories); * tell libpq where to find the socket, e.g. with "psql -h /tmp"; * tell libpq not to use a Unix socket at all, e.g. "psql -h localhost"; * make sure to invoke psql+libpq from the PGDG distribution rather than using ones supplied by Red Hat. Generally speaking, mixing PGDG RPMs with vendor-supplied Postgres RPMs is a recipe for headaches. If you can drop the Red Hat Postgres RPMs without causing dependency problems, do that. Otherwise, the two-socket-files solution is probably the best. regards, tom lane
Re: A query in Streaming Replication
Hello, Can anyone check this and respond please!! Thanks and regards, Manoj. On Sat, Jun 13, 2020, 20:26 Sreerama Manoj wrote: > Hello, > I use streaming replication in async mode. When master gets down, > slave will be promoted using a trigger file. During this process ".partial" > file will be created and a WAL file with same ID will be created in a > different time line in slave. When master comes back as slave, it will be > synced to the current master. This is the normal procedure as far as I > understood. But in some cases, ".partial" file is not getting created and > peer DB which comes back as slave is unable to sync when this happens.. > Please suggest if this happens in any scenario and how to overcome this. > > Thanks & Regards: > Manoj. > > >
