Re: 20220722-pg_dump: error: invalid number of parents 0 for table
hey Karthick, if you've made triple sure that it's not a permissions issue, take a look at some of the suggestions in this old thread: https://dba.stackexchange.com/questions/17546/why-is-pg-dumpall-throwing-an-oid-does-not-exist-error On Mon, Aug 8, 2022 at 5:30 AM Techsupport wrote: > *Hi to all,* > > > > We are using PostgreSQL 12.3 and it is running under Windows environment. > In my server multiple databases are available. > > > > When we try to Back up one of the database, it gives the following error > message, we can able to connect and read & write to the database. > > But unable to back up the database , We faced this issue on one database > only, remaining databases are working good and able to backup. Suggest me, > How to resolve this issue. > > > > *pg_dump**: last built-in OID is 16383* > > *pg_dump**: reading extensions* > > *pg_dump**: identifying extension members* > > *pg_dump**: reading schemas* > > *pg_dump**: reading user-defined tables* > > *pg_dump**: reading user-defined functions* > > *pg_dump**: reading user-defined types* > > *pg_dump**: error: schema with OID 1020797 does not exist* > > > > *Thanks,* > > *Karthick Ramu* >
Re: What is the best setup for distributed and fault-tolerant PG database?
What Rob said... plus, I would urge you to give some more thought to "for audit purposes the data is append only". If your application is ever successful, non-insignificant storage costs are something you'll need to deal with sooner or later. Anyway, what you ask is certainly achievable, but not without sustained effort. IMO your options are: spend the time to learn on your own with much reading + trial and error; pay somebody to set it up for you; or, cross the high-availability bridge after you've got something of substance developed, app-wise, on a single local DB. Best of luck! On Wed, Jan 4, 2023, 6:54 p.m. Rob Sargent wrote: > On 1/4/23 06:26, Age Apache wrote: > > Dear PG experts, > > I am new to postgres, and I am also not a DBA. I am a solo developer who > is trying to evaluate what database to use for my hybrid multi-tenancy > sub-apps i.e. users of the application will be authorised to use part or > whole of the application based on their authorisation levels. This > delineation of user access has to also be supported by the database, if > possible. Also, for audit purposes the data is append only. And the design > is based on just two tables(vertices and edges) to emulate a > document-oriented(jsonb) graph structure. > > Postgres is the database I am leaning towards for this project. But as I > am not a DBA and also a solo developer, I am trying to understand how I can > spend less time managing the DB and more time developing the application. I > would like to have a distributed and fault-tolerant DB setup with multiple > read and write nodes with little to no configuration on my part, if > possible. I am looking for a self-hosted open source solution. > > Is this possible with PG? What is the best way to achieve this for a > non-DBA solo developer like me? > > Thanks and kind regards > > None of the experts chimed in so I ante up my $0.02. > > It won't be possible unless you become a serious DBA _and_ solo (full > stack) developer. Or you pay for db support. > > >
Re: Manual failover cluster
Hi Moishe, Since we use pgbackrest ourselves, this is the process I followed to set up something similar on PG 10: https://pgstef.github.io/2018/11/28/combining_pgbackrest_and_streaming_replication.html (Not knowing much [if at all] about the reason for your requirements, I would recommend looking into enabling PITR backups in addition to the hot standby[s], which as you've seen are not bulletproof.) Anyway, just wanted to add that your experience roughly matches ours -- it seems that an attempt to promote the standby isn't always smooth. My sample size is almost certainly smaller than yours, about half a dozen attempts. In the past I've attributed this to a poor understanding on my part of the behind-the-scenes of the process, and chalked it up to having made a mistake or other in the manual steps (mainly in the order they are executed). That being said, if you find a way to faithfully reproduce the issue, I'm sure the community will want to know, there is certainly an expectation that the failover is reliable from the PG software side of things, as long as there are no hardware nor operator issues! Again, not knowing a lot about your setup, my first instinct would be to troubleshoot your automated scripts, you might find that you need to change the order things are run when on server B vs server A, for example.. On Fri, Aug 20, 2021 at 9:23 AM Hispaniola Sol wrote: > Team, > > I have a pg 10 cluster with a master and two hot-standby nodes. There is a > requirement for a manual failover (nodes switching the roles) at will. This > is a vanilla 3 node PG cluster that was built with WAL archiving (central > location) and streaming replication to two hot standby nodes. The failover > is scripted in Ansible. Ansible massages and moves around the > archive/restore scripts, the conf files and the trigger and calls ` > pg_ctlcluster` to start/stop. This part _seems_ to be doing the job fine. > > The issue I am struggling with is the apparent fragility of the process - > all 3 nodes will end up in a "good" state after the switch only every other > time. Other times I have to rebase the hot-standby from the new master with > pg_basebackup. It seems the issues are mostly with those nodes, ending up > as slaves after the roles switch runs. > They get errors like mismatch in timelines, recovering from the same WAL > over and over again, invalid resource manager ID in primary checkpoint > record, etc. > > In this light, I am wondering - using what's offered by PostgreSQL itself, > i.e. streaming WAL replication with log shipping - can I expect to have > this kind of failover 100% reliable on PG side ? Anyone is doing this > reliably on PostgreSQL 10.1x ? > > Thanks ! > > Moishe >
Re: Manual failover cluster
Sorry, I misspoke there - I meant to say that since one should not count on the standby-failover process to always run smoothly (whether it's due to hardware, operator, automated scripts, or software issues), DB backups should also be in place if at all possible. On Mon, Aug 23, 2021 at 1:37 PM Saul Perdomo wrote: > Hi Moishe, > > Since we use pgbackrest ourselves, this is the process I followed to set > up something similar on PG 10: > > > https://pgstef.github.io/2018/11/28/combining_pgbackrest_and_streaming_replication.html > > (Not knowing much [if at all] about the reason for your requirements, I > would recommend looking into enabling PITR backups in addition to the hot > standby[s], which as you've seen are not bulletproof.) > > Anyway, just wanted to add that your experience roughly matches ours -- it > seems that an attempt to promote the standby isn't always smooth. My sample > size is almost certainly smaller than yours, about half a dozen attempts. > In the past I've attributed this to a poor understanding on my part of the > behind-the-scenes of the process, and chalked it up to having made a > mistake or other in the manual steps (mainly in the order they are > executed). That being said, if you find a way to faithfully reproduce the > issue, I'm sure the community will want to know, there is certainly an > expectation that the failover is reliable from the PG software side of > things, as long as there are no hardware nor operator issues! Again, not > knowing a lot about your setup, my first instinct would be to troubleshoot > your automated scripts, you might find that you need to change the order > things are run when on server B vs server A, for example.. > > On Fri, Aug 20, 2021 at 9:23 AM Hispaniola Sol > wrote: > >> Team, >> >> I have a pg 10 cluster with a master and two hot-standby nodes. There is >> a requirement for a manual failover (nodes switching the roles) at will. >> This is a vanilla 3 node PG cluster that was built with WAL archiving >> (central location) and streaming replication to two hot standby nodes. The >> failover is scripted in Ansible. Ansible massages and moves around the >> archive/restore scripts, the conf files and the trigger and calls ` >> pg_ctlcluster` to start/stop. This part _seems_ to be doing the job fine. >> >> The issue I am struggling with is the apparent fragility of the process - >> all 3 nodes will end up in a "good" state after the switch only every other >> time. Other times I have to rebase the hot-standby from the new master with >> pg_basebackup. It seems the issues are mostly with those nodes, ending up >> as slaves after the roles switch runs. >> They get errors like mismatch in timelines, recovering from the same WAL >> over and over again, invalid resource manager ID in primary checkpoint >> record, etc. >> >> In this light, I am wondering - using what's offered by PostgreSQL >> itself, i.e. streaming WAL replication with log shipping - can I expect to >> have this kind of failover 100% reliable on PG side ? Anyone is doing this >> reliably on PostgreSQL 10.1x ? >> >> Thanks ! >> >> Moishe >> >
Re: Manual failover cluster
Are you sure that it is *mandatory*? Because from my recollection I've only needed to set one manually when (for one reason or another) my recovery attempt fails and then I'm in what the docs call a "complex re-recovery situation" -- not a fun time: recovery_target_timeline (string) Specifies recovering into a particular timeline. The default is to recover along the same timeline that was current when the base backup was taken. Setting this to latest recovers to the latest timeline found in the archive, which is useful in a standby server. Other than that you only need to set this parameter in complex re-recovery situations, where you need to return to a state that itself was reached after a point-in-time recovery. See Section 25.3.5 <https://www.postgresql.org/docs/10/continuous-archiving.html#BACKUP-TIMELINES> for discussion. From: https://www.postgresql.org/docs/10/recovery-target-settings.html On Fri, Aug 27, 2021 at 2:36 AM Ninad Shah wrote: > Hi Saul, > > Hope you are doing well. My apology for no response for a longer time. > > Pgbackrest helps build a streaming replication. While performing role > reversal(switchover), it is mandatory to set recover_target_timeline to > latest in recovery.conf(in data directory). Steps to perform switchover is > as below. > > 1) Stop master database > 2) Promote the slave database > 3) Prepare the recovery.conf file on the stopped master node, and set > recover_target_timeline to latest in that file > 4) Start the stopped master database; it will automatically come up as a > slave > 5) Check status of the database. > > Additionally, entries in pg_hba.conf is required. > > Hope this helps. > > > Regards, > Ninad Shah > > > On Mon, 23 Aug 2021 at 23:12, Saul Perdomo wrote: > >> Sorry, I misspoke there - I meant to say that since one should not count >> on the standby-failover process to always run smoothly (whether it's due to >> hardware, operator, automated scripts, or software issues), DB backups >> should also be in place if at all possible. >> >> On Mon, Aug 23, 2021 at 1:37 PM Saul Perdomo >> wrote: >> >>> Hi Moishe, >>> >>> Since we use pgbackrest ourselves, this is the process I followed to set >>> up something similar on PG 10: >>> >>> >>> https://pgstef.github.io/2018/11/28/combining_pgbackrest_and_streaming_replication.html >>> >>> (Not knowing much [if at all] about the reason for your requirements, I >>> would recommend looking into enabling PITR backups in addition to the hot >>> standby[s], which as you've seen are not bulletproof.) >>> >>> Anyway, just wanted to add that your experience roughly matches ours -- >>> it seems that an attempt to promote the standby isn't always smooth. My >>> sample size is almost certainly smaller than yours, about half a dozen >>> attempts. In the past I've attributed this to a poor understanding on my >>> part of the behind-the-scenes of the process, and chalked it up to having >>> made a mistake or other in the manual steps (mainly in the order they are >>> executed). That being said, if you find a way to faithfully reproduce the >>> issue, I'm sure the community will want to know, there is certainly an >>> expectation that the failover is reliable from the PG software side of >>> things, as long as there are no hardware nor operator issues! Again, not >>> knowing a lot about your setup, my first instinct would be to troubleshoot >>> your automated scripts, you might find that you need to change the order >>> things are run when on server B vs server A, for example.. >>> >>> On Fri, Aug 20, 2021 at 9:23 AM Hispaniola Sol >>> wrote: >>> >>>> Team, >>>> >>>> I have a pg 10 cluster with a master and two hot-standby nodes. There >>>> is a requirement for a manual failover (nodes switching the roles) at will. >>>> This is a vanilla 3 node PG cluster that was built with WAL archiving >>>> (central location) and streaming replication to two hot standby nodes. The >>>> failover is scripted in Ansible. Ansible massages and moves around the >>>> archive/restore scripts, the conf files and the trigger and calls ` >>>> pg_ctlcluster` to start/stop. This part _seems_ to be doing the job fine. >>>> >>>> The issue I am struggling with is the apparent fragility of the process >>>> - all 3 nodes will end up in a "good" state after the switch only every >>>> other time. Other times I have to rebase the hot-standby
Re: Managing major PostgreSQL upgrades
Hey Tiff, We're in a similar boat. We currently lean on (mostly custom) ansible scripting to automate all the repeatable tasks we can, but automation of major PG version upgrades is something we are yet to tackle -- although we plan to start this effort in the short term. Would you mind sharing a bit more about your own current upgrade process? What's your standard, a dump+restore, a pg_upgrade, or replication-based? Also if you are able to share any lessons learned (e.g. common pitfalls you've run into) will all be useful information to identify ahead of time when drafting an automation strategy. On Mon., Nov. 15, 2021, 6:45 a.m. Tiffany Thang, wrote: > Hi, > Every year we spent a lot of time planning and manually performing major > PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering > if there is a better way of managing these upgrades through automation. Can > anyone share their experiences? > > Thanks. > > Tiff > > > >
Re: Managing major PostgreSQL upgrades
Thanks. My first instinct might be to simply maintain your own list of missing packages, and ensure they all get installed prior to the pg_upgrade run (something which ansible can handily do). Now, depending on the level of homogeneity of the server farm under your purview, this might pose its own challenges - but based on this info, it's where I would probably start.. On Wed., Nov. 17, 2021, 12:28 a.m. Tiffany Thang, wrote: > On Mon, Nov 15, 2021 at 8:48 PM Saul Perdomo > wrote: > >> Hey Tiff, >> >> We're in a similar boat. We currently lean on (mostly custom) ansible >> scripting to automate all the repeatable tasks we can, but automation of >> major PG version upgrades is something we are yet to tackle -- although we >> plan to start this effort in the short term. >> >> Would you mind sharing a bit more about your own current upgrade process? >> What's your standard, a dump+restore, a pg_upgrade, or replication-based? >> Also if you are able to share any lessons learned (e.g. common pitfalls >> you've run into) will all be useful information to identify ahead of time >> when drafting an automation strategy. >> > > Our upgrades (pg_upgrade) have been pretty smooth so far. We have not > encountered any issues other than the occasional missing OS packages. The > issue is I do not know what problems to expect during a major upgrade which > makes automating the process difficult. > > >> >> On Mon., Nov. 15, 2021, 6:45 a.m. Tiffany Thang, >> wrote: >> >>> Hi, >>> Every year we spent a lot of time planning and manually performing major >>> PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering >>> if there is a better way of managing these upgrades through automation. Can >>> anyone share their experiences? >>> >>> Thanks. >>> >>> Tiff >>> >>> >>> >>>
Re: Postgres listens on random port
" But the database actually listens on some random port like 20011" How exactly did you determine this? (If you used, say, netstat, can you share its output with us?) On Tue, Nov 5, 2024 at 8:46 AM Kal wrote: > Hi > > I have a scenario in which postgres listens on a random port different > from the one which was specified in pg_ctl start command. > > This occurs in windows. When postgres is started on port 5460, the logs > denote that postgres is started on 5460. But the database actually listens > on some random port like 20011 > > When the same postgres is started as a service,it listens on the intended > port. > > I have tried with both normal and admin users. > > Any suggestions on this behaviour will be helpful. > > > Regards > Kal >
Re: Postgres listens on random port
Agreed - it sounds like OP found a *client *process and mistook it for a server process, which is why I wanted to know where exactly he saw this. On Tue, Nov 5, 2024 at 9:41 AM Ron Johnson wrote: > On Tue, Nov 5, 2024 at 9:22 AM Kal wrote: > >> >> On Tue, 5 Nov 2024 at 7:42 PM, Greg Sabino Mullane >> wrote: >> >>> Start by seeing where the port is actually being set by running this: >>> >>> select setting, source, sourcefile, sourceline from pg_settings where >>> name = 'port'; >>> Cheers, >>> Greg >>> >>> Hi Greg, >> >> The query output states the source as command line. Event the port value >> output from show port is same as given in pg_ctl command. >> >> But the database is listening on some other random port. >> > > *Client* applications use a random port on *their* host: In this case, > 10.176.242.216 is the client, and 10.109.165.4, and 10.176.252.201 are > the DB servers: > > $ netstat -an | grep :5432 > tcp0 0 10.176.242.216:3708610.109.165.4:5432 > ESTABLISHED > tcp0 0 10.176.242.216:4325610.176.252.201:5432 > TIME_WAIT > tcp0 0 10.176.242.216:3617210.143.170.55:5432 > TIME_WAIT > tcp0 0 10.176.242.216:3614210.143.170.55:5432 > TIME_WAIT > tcp0 0 10.176.242.216:3615610.143.170.55:5432 > TIME_WAIT > tcp0 0 10.176.242.216:3617410.143.170.55:5432 > TIME_WAIT > tcp0 0 10.176.242.216:3899810.109.165.4:5432 > TIME_WAIT > tcp0 0 10.176.242.216:3618610.143.170.55:5432 > TIME_WAIT > tcp0 0 10.176.242.216:3618810.143.170.55:5432 > ESTABLISHED > tcp 19 0 10.176.242.216:3619610.143.170.55:5432 > ESTABLISHED > tcp 19 0 10.176.242.216:3620810.143.170.55:5432 > ESTABLISHED > tcp0 0 10.176.242.216:3621010.143.170.55:5432 > ESTABLISHED > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > crustacean! >
Re: Query about pg_wal directory filled up
On Wed, Dec 18, 2024, 5:01 a.m. Rama Krishnan wrote: > > Hi Team, > > One of our clients recently migrated from Oracle to PostgreSQL and is > conducting multiple scenarios to understand PostgreSQL behavior. > > One of their main concerns is whether the database will allow `INSERT` > queries when the `pg_wal` directory becomes full. > > The way out of this "conundrum" is to make sure to implement a solid backup process. I'm a huge fan of PGBackRest: https://pgbackrest.org/ > Kindly advise > > > Thanks > RK >
Re: How to deal with dangling files after aborted `pg_restore`?
Genuine question: Why are you looking to recover from this half-cooked state instead of restarting the restore process from the beginning? On Tue, Dec 17, 2024, 1:10 a.m. Ivan Kurnosov wrote: > The scenario: > > 1. There is a postgresql 17 server running > 2. Restore dump with `--single-transaction` flag > 3. For whatever reason the server goes away (eg: we kill the process) > 4. Now `base` directory is filled with abandoned table files which > postgresql know nothing about > > Playground: > > Terminal 1: > Start server: `docker run --rm -it -e POSTGRES_HOST_AUTH_METHOD=trust -v > $PWD/postgres:/var/lib/postgresql/data postgres:17.2` > > Terminal 2: > 1. Start container with recent pg_restore: `docker run --rm -it -v > $PWD:/app -w /app postgres:17.2 bash` > 2. Start dump recovery: `pg_restore -h 172.17.0.2 -U postgres -d postgres > --single-transaction -v -Fc --no-owner dump.sql` > > Terminal 3: > 1. Find what container is the server: `docker ps` > 2. Kill it: `docker kill d7ecf6e66c1d` > > Terminal 1: > Start the server again, with the same command > > Terminal 3: > Check there are abandoned large files: > ``` > # ls -la /home/ivan/postgres/base/5 > > -rw--- 1 systemd-coredump systemd-coredump 342884352 Dec 17 18:58 16399 > -rw--- 1 systemd-coredump systemd-coredump 11149312 Dec 17 18:58 16404 > -rw--- 1 systemd-coredump systemd-coredump188416 Dec 17 18:58 > 16403_fsm > -rw--- 1 systemd-coredump systemd-coredump 686145536 Dec 17 18:58 16403 > ``` > > Terminal 2: > 1. Confirm those OIDs are not accounted: > ``` > psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16404 > psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16403 > psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16399 > ``` > > Hence a question: am I doing something wrong? Is it expected behaviour? If > so - how would one recover from this scenario now WITHOUT dropping entire > database? > > -- > With best regards, Ivan Kurnosov >
Re: Return of the pg_wal issue..
Thanks for the correction Adrian - my oversimplification went too far, and into "plain wrong" territory. (The detail that I felt was too much for this explanation was: "and the way to simply get rid of them would be to set your archive command to '/bin/true', say".. but didn't want to make it seem like I was suggesting Paul do that) On Thu, Jan 23, 2025, 11:07 a.m. Adrian Klaver wrote: > On 1/23/25 06:51, Saul Perdomo wrote: > > > This is why everybody will tell you "don't just delete these files, > > archive them properly!" Again, for operational purposes, you could just > > delete them. But you really want to make a /copy /of them before you > > do... you know, /just in case /something bad happens to your DB that > > makes you want to roll it back in time. > > No you can't just delete them for operational purposes without knowledge > of whether they are still needed or not. > > Per: > > https://www.postgresql.org/docs/current/wal-intro.html > > and > > https://www.postgresql.org/docs/current/wal-configuration.html > > Short version, a WAL file must remain until a checkpoint is done that > makes it's content no longer needed. > > > Cheers > > Saul > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
