Re: PostgreSQL logical replication depends on WAL segments?
Hi guys, thank you very much for all information. I learned my lesson regarding cronjob cleaning old WAL logs... There is one other interesting problem I have found today and I would like to ask you about you opinion. On logical master I found this morning big flood of these messages in postgresql log: 2019-01-23 08:17:42.338 UTC [1310] WARNING: oldest xmin is far in the past 2019-01-23 08:17:42.338 UTC [1310] HINT: Close open transactions soon to avoid wraparound problems. You might also need to commit or roll back old prepared transactions, or drop stale replication slots. Since this is still testing environment I dropped subscription and publication and logical replication slot and messages stopped. But do you know what was wrong? Why these warnings? Did logical replication slot blocked autovacuum or something else happened? Can I prevent it? As mentioned before we have quite huge amount of data, around 500 millions of records every day, most of them inserted in separate transactions or in only small blocks so I guess xmin value can grow very quickly... Thanks jm On Tue, 22 Jan 2019 at 20:03, Jeremy Finzel wrote: > Note replication slots only prevent old *catalog* rows from being >> removed, not old row versions in user created tables. >> > > Thank you for that clarification. I can see this is noted clearly in the > CAUTION statement here: > https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS > > Thanks, > Jeremy >
Reclaiming space for dropped database
Hi all, Our current development database server is running a bit low on diskspace, so I dropped an old but rather large database with the intention of claiming back some space. However, the space remains claimed. This server was upgraded from PG10 to PG11 using pg_upgrade's --link option. I see this after having dropped the (300+GB) database: -bash-4.2$ du -d 1 -h 4.0K./.cache 0 ./.config 4.0K./9.6 376G./10 72G ./11 447G. The files of that database apparently still exist within the ./10 directory: -bash-4.2$ du -d 1 -h ./10/data/ 4.1G./10/data/pg_wal 816K./10/data/global 0 ./10/data/pg_commit_ts 0 ./10/data/pg_dynshmem 8.0K./10/data/pg_notify 0 ./10/data/pg_serial 0 ./10/data/pg_snapshots 208K./10/data/pg_subtrans 0 ./10/data/pg_twophase 16K ./10/data/pg_multixact 372G./10/data/base 0 ./10/data/pg_replslot 0 ./10/data/pg_tblspc 3.5M./10/data/pg_stat 0 ./10/data/pg_stat_tmp 4.0K./10/data/pg_logical 96K ./10/data/log 688K./10/data/pg_xact 376G./10/data/ How do I reclaim that space? Regards, Alban Hertroys Alban Hertroys D: +31 (0)53 4 888 888 | T: +31 (0)53 4888 888 | E: alban.hertr...@apollovredestein.com Apollo Vredestein B.V.| Ir. E.L.C. Schiffstraat 370, 7547 RD Enschede, The Netherlands Chamber of Commerce number: 34223268 The information contained in this e-mail is intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the contents of this information is strictly prohibited and may be unlawful and request you to delete this message and any attachments and advise the sender by return e-mail. The confidentiality of this message is not warranted. Apollo Vredestein and its subsidiaries rule out any and every liability resulting from this or any other electronic transmission Please consider the environment before printing this e-mail
Re: Reclaiming space for dropped database
Alban Hertroys writes: > Our current development database server is running a bit low on diskspace, > so I dropped an old but rather large database with the intention of > claiming back some space. However, the space remains claimed. > This server was upgraded from PG10 to PG11 using pg_upgrade's --link > option. If you used --link, then all the files would remain hard-linked from both the old and new database directories. You've got to remove them from the old DB directory as well. There's not really any point in keeping around the source DB directory once you've completed a --link migration. Starting the postmaster in the old DB directory would be disastrous because the files are inconsistent from its standpoint once the new postmaster has modified them at all. (In fact, I think pg_upgrade intentionally makes the old directory non-runnable to prevent that error.) So you might as well just "rm -rf ./10", not only its biggest subdirectory. regards, tom lane
Re: PostgreSQL logical replication depends on WAL segments?
Thanks, I see... So if I understand it correctly - since I have quite big >> partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in >> several others I presume I had to set wal_keep_segments to some really high >> number and stop our security cronjob cleaning old WAL segments (because we >> already had some problems with almost full disk due to old WAL segments) >> until the whole transfer of snapshot is done. Because only after the whole >> snapshot is transferred logical replication workers start to transfer WAL >> logs reflecting changes done from the moment snapshot was taken... >> >> jm >> > > Understand there are other downsides to just keeping around a huge amount > of WAL segments apart from only taking up disk space. None of the data > held in those WAL segments can be vacuumed away while they are left around, > which can lead to significant bloat and performance issues over time. > > I'm not exactly clear on your use case, but if you need to just > resychronize data for a single table, there is a built-in way to do that > (actually would be nice if the docs spelled this out). > > On publisher: > > ALTER PUBLICATION mypub DROP TABLE old_data_table; > > On subscriber: > > ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true); > > On publisher: > > ALTER PUBLICATION mypub ADD TABLE old_data_table; > > On subscriber: > > ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true); > > Many thanks Jeremy, I changed my implementation based on your recommendation and now it works like a charm for all available tables. > The last command will resync the table from the current table data, > regardless of the WAL file situation. This is the "normal" way you would > go about resynchronizing data between clusters when a long time has passed, > rather than trying to keep all that WAL around! > > So far as I can tell from testing, above pattern is the easiest way to do > this, and it will not resynchronize any of the other tables in your > subscription. > > P.S. do heed the advice of the others and get more familiar with the docs > around WAL archiving. > > Thanks, > Jeremy >
Betr: Re: Reclaiming space for dropped database
"Tom Lane" wrote on 2019-01-23 16:02:01: > Alban Hertroys writes: > > Our current development database server is running a bit low on diskspace, > > so I dropped an old but rather large database with the intention of > > claiming back some space. However, the space remains claimed. > > This server was upgraded from PG10 to PG11 using pg_upgrade's --link > > option. > > If you used --link, then all the files would remain hard-linked from both > the old and new database directories. You've got to remove them from the > old DB directory as well. > > There's not really any point in keeping around the source DB directory > once you've completed a --link migration. Starting the postmaster in > the old DB directory would be disastrous because the files are > inconsistent from its standpoint once the new postmaster has modified > them at all. (In fact, I think pg_upgrade intentionally makes the old > directory non-runnable to prevent that error.) So you might as well > just "rm -rf ./10", not only its biggest subdirectory. That explains what I'm seeing. After creating a dump (better safe than sorry), I'll remove that directory. Thanks! Regards, Alban. Alban Hertroys D: +31 (0)53 4 888 888 | T: +31 (0)53 4888 888 | E: alban.hertr...@apollovredestein.com Apollo Vredestein B.V.| Ir. E.L.C. Schiffstraat 370, 7547 RD Enschede, The Netherlands Chamber of Commerce number: 34223268 The information contained in this e-mail is intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the contents of this information is strictly prohibited and may be unlawful and request you to delete this message and any attachments and advise the sender by return e-mail. The confidentiality of this message is not warranted. Apollo Vredestein and its subsidiaries rule out any and every liability resulting from this or any other electronic transmission Please consider the environment before printing this e-mail
Monitoring PITR recovery progress
Hello, I got a sort of POLA violation moment today - a colleague has restored a PITR archive up to a point in time, and when the developers looked at the data, it looked wrong - as it if wasn't from that particular time. Later, he told me he got an error trying to use pg_dump to extract the desired tables to restore: Dumping the contents of table "xxx" failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. This could be the source of data problems the devs are seeing. Offhand, this looks like it's indicating a conflict between the PITR recovery process and pg_dump. But, something's puzzling to me: why did he manage to connect to the db at all? And, the actual question: how to monitor the WAL replay process? Currently, the recovery.conf file is sitting there, with the database running, but pg processes are idle, and pg_stat_activity doesn't list anything which appears to be related to the recovery process.
Re: Monitoring PITR recovery progress
On Wed, 2019-01-23 at 18:58 +0100, Ivan Voras wrote: > And, the actual question: how to monitor the WAL replay process? > Currently, the recovery.conf file is sitting there, with the database > running, but pg processes are idle, and pg_stat_activity doesn't list > anything which appears to be related to the recovery process. > > > The server logs each wal segment that gets processed during recovery. And you would definitely see a busy high-I/IO process applying the recovery. It also logs when the recovery is complete. And I'm pretty sure it renames recovery.conf to recovery.done or something when it's done.
Re: Reclaiming space for dropped database
Tom Lane writes: > Alban Hertroys writes: > >> Our current development database server is running a bit low on diskspace, >> so I dropped an old but rather large database with the intention of >> claiming back some space. However, the space remains claimed. >> This server was upgraded from PG10 to PG11 using pg_upgrade's --link >> option. > > If you used --link, then all the files would remain hard-linked from both > the old and new database directories. You've got to remove them from the > old DB directory as well. > > There's not really any point in keeping around the source DB directory > once you've completed a --link migration. Starting the postmaster in > the old DB directory would be disastrous because the files are > inconsistent from its standpoint once the new postmaster has modified > them at all. (In fact, I think pg_upgrade intentionally makes the old > directory non-runnable to prevent that error.) So you might as well Yeah. IIRC, it renames control to pg_control.old to avoid accidental startup. > just "rm -rf ./10", not only its biggest subdirectory. > > regards, tom lane > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
how to properly start postgresql with no TCP listeners in ubuntu 16.04 LTS
Greetings, This question may simply be my ignorance of what piece of the systemd / systemctl puzzle needs attention. Any clues are appreciated. Using postgreSQL version 9.5 as obtained from ubuntu repo. Install was fine. initdb and subsequent database use is fine. Nothing is out of the ordinary except me being able to alter how postgres starts on boot (eg how postgres is called by init scripts or systemd ). I wish to start postgres on boot with no TCP listeners, only unix-domain sockets. I can achieve this goal by executing the following command as postgres user in a normal terminal session; postgres -D /space/postgres -h '' -c listen_addresses='' Starting the database in this manner results in stdout and stderr from postgres to show up in the terminal and not the default log file, but I can fix that. Stopping the database cleanly when started in the above manner requires user postgres to give 'pg_ctl stop' command in another terminal. Of course I can press ctrl-C in the terminal it was started from, and it dies.. I can also achieve the no TCP socket goal using a pg_ctl command, as postgres in a terminal; pg_ctl start -D /space/postgres -o '-h "" -c listen_addresses=""' where in the pg_ctl command, the single quotes protect the double quotes, which are placeholders for the empty parameter values needing to be passed to the actual postgres command.. I thought I could edit the postgresql.service / postgresql@.service files and use the systemctl method. I have not yet discovered a way to pass the needed options and empty quotes, or any "special character" (special as seen by systemd I guess when reading in postgresql*.service files..) into the startup process. Postgres will start but it's the default install values, and not my needed command line. Seems like there ought to be a way to do what I need. I just haven't found it yet. Suggestions on what systemctl magic fruits or other system startup tool needs attention are most welcome. Thanks in advance, alex
Re: how to properly start postgresql with no TCP listeners in ubuntu 16.04 LTS
Greetings, * Alex Morris (alex.mor...@twelvemountain.com) wrote: > This question may simply be my ignorance of what piece of the systemd / > systemctl puzzle needs attention. Any clues are appreciated. The simplest approach is to just modify the postgresql.conf file in /etc/postgresql/9.5/main to update listen_addresses to be ''. Thanks! Stephen signature.asc Description: PGP signature
Re: how to properly start postgresql with no TCP listeners in ubuntu 16.04 LTS
On 1/23/19 19:15, Stephen Frost wrote: Greetings, * Alex Morris (alex.mor...@twelvemountain.com) wrote: This question may simply be my ignorance of what piece of the systemd / systemctl puzzle needs attention. Any clues are appreciated. The simplest approach is to just modify the postgresql.conf file in /etc/postgresql/9.5/main to update listen_addresses to be ''. Thanks! Stephen Thanks for the fast reply. I had tried that earlier with no luck. But it's working properly now. I must have misspelled listener_addresses or used incorrect quote marks. My issue is resolved. Joy! Thanks very much, Stephen!
Re: how to properly start postgresql with no TCP listeners in ubuntu 16.04 LTS
On 1/23/19 8:41 PM, Alex Morris wrote: On 1/23/19 19:15, Stephen Frost wrote: Greetings, * Alex Morris (alex.mor...@twelvemountain.com) wrote: This question may simply be my ignorance of what piece of the systemd / systemctl puzzle needs attention. Any clues are appreciated. The simplest approach is to just modify the postgresql.conf file in /etc/postgresql/9.5/main to update listen_addresses to be ''. Thanks! Stephen Thanks for the fast reply. I had tried that earlier with no luck. But it's working properly now. I must have misspelled listener_addresses or used incorrect quote marks. My issue is resolved. Joy! Note that Postgres has a repository for Ubuntu. From there, you can install 9.6, 10 and 11. https://www.postgresql.org/download/linux/ubuntu/ -- Angular momentum makes the world go 'round.
