Re: [HACKERS] pg_receivexlog does not report flush position with --synchronous
Hi Simon and Michael, 2016-08-23 10:39 GMT+02:00 Simon Riggs : > > Agreed, but I'd move all the comments above the block. > That's fine with me. Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia - Director PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it <http://www.2ndquadrant.it/>
[HACKERS] pg_receivexlog does not report flush position with --synchronous
Hi guys, while adding synchronous WAL streaming to Barman, I noticed that pg_receivexlog - unless a replication slot is specified and --synchronous is passed - does not become a synchronous receiver (if the application_name is in the synchronous_standby_names value). I was a bit surprised by this behaviour. By reading the pg_receivexlog documentation, I assumed that: 1) if I set application_name properly for pg_receivexlog (let's say 'barman_receive_wal') 2) then I set synchronous_standby_names so that barman_receive_wal is first in the list 3) then I run pg_receivexlog with --synchronous I would find the pg_receivexlog in 'sync' state in the pg_stat_replication view on the master. However, I kept receiving the 'async' state. After looking at the documentation once more, I noticed that '--synchronous' was mentioned also in the '--slot-name' option but the explanation - at least to me - was not very clear. I tried again by creating a replication slot and passing it to pg_receivexlog and this time I could see 'sync' as streaming state. Looking up the code in more details I see that, unless replication slot are enabled, pg_receivexlog does not report the flush position (this is a precaution that's been taken when '--synchronous' was probably not around). Please find this very short patch which - in case replication slots are not present but synchronous is - reports the flush position. I am not sure if it is a bug or not. I any case I guess we should probably improve the documentation - it's a bit late here so maybe I can try better tomorrow with a fresher mind. :) Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia - Director PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it 0001-pg_receivexlog-does-not-report-flush-position-with-s.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] File based Incremental backup v8
Hi Robert, 2015-03-07 2:57 GMT+11:00 Robert Haas : > By the way, unless I'm missing something, this patch only seems to > include the code to construct an incremental backup, but no tools > whatsoever to do anything useful with it once you've got it. As stated previously, Marco is writing a tool called pg_restorebackup (the prototype in Python has been already posted) to be included in the core. I am in Australia now and not in the office so I cannot confirm it, but I am pretty sure he had already written it and was about to send it to the list. He's been trying to find more data - see the one that he's sent - in order to convince that even a file-based approach is useful. I think that's 100% unacceptable. I agree, that's why pg_restorebackup written in C is part of this patch. See: https://wiki.postgresql.org/wiki/Incremental_backup > Users need to be able to manipulate > PostgreSQL backups using either standard operating system tools or > tools provided with PostgreSQL. Some people may prefer to use > something like repmgr or pitrtools or omniptr in addition, but that > shouldn't be a requirement for incremental backup to be usable. > Not at all. I believe those tools will have to use pg_basebackup and pg_restorebackup. If they want to use streaming replication protocol they will be responsible to make sure that - if the protocol changes - they adapt their technology. Agile development is good, but that does not mean you can divide a big > project into arbitrarily small chunks. At some point the chunks are > too small to be sure that the overall direction is right, and/or > individually useless. > The goal has always been to provide "file-based incremental backup". I can assure that this has always been our compass and the direction to follow. I repeat that, using pg_restorebackup, this patch will transparently let users benefit from incremental backup even when it will be moved to an internal block-level logic. Users will continue to execute pg_basebackup and pg_restorebackup, ignoring that with - for example 9.5 - it is file-based (saving between 50-70% of space and time) of block level - for example 9.6. My proposal is that Marco provides pg_restorebackup according to the initial plan - a matter of hours/days. Cheers, Gabriele
Re: [HACKERS] File based Incremental backup v8
Hi Bruce, 2015-03-08 5:37 GMT+11:00 Bruce Momjian : > > Desirability -> Design -> Implement -> Test -> Review -> Commit > > This patch has continued in development without getting agreement on > its Desirability or Design, meaning we are going to continue going back > to those points until there is agreement. Posting more versions of this > patch is not going to change that. > Could you please elaborate that? I actually think the approach that has been followed is what makes open source and collaborative development work. The initial idea was based on timestamp approach which, thanks to the input of several developers, led Marco to develop LSN based checks and move forward the feature implementation. The numbers that Marco has posted clearly show that a lot of users will benefit from this file-based approach for incremental backup through pg_basebackup. As far as I see it, the only missing bit is the pg_restorebackup tool which is quite trivial - given the existing prototype in Python. Thanks, Gabriele
Re: [HACKERS] File based Incremental backup v8
Hi Robert, 2015-03-06 3:10 GMT+11:00 Robert Haas : > But I agree with Fujii to the extent that I see little value in > committing this patch in the form proposed. Being smart enough to use > the LSN to identify changed blocks, but then sending the entirety of > every file anyway because you don't want to go to the trouble of > figuring out how to revise the wire protocol to identify the > individual blocks being sent and write the tools to reconstruct a full > backup based on that data, does not seem like enough of a win. I believe the main point is to look at a user interface point of view. If/When we switch to a block level incremental support, this will be completely transparent to the end user, even if we start with a file-level approach with LSN check. The win is already determined by the average space/time gained by users of VLDB with a good chunk of read-only data. Our Barman users with incremental backup (released recently - its algorithm can be compared to the one of file-level backup proposed by Marco) can benefit on average of a data deduplication ratio ranging between 50 to 70% of the cluster size. A tangible example is depicted here, with Navionics saving 8.2TB a week thanks to this approach (and 17 hours instead of 50 for backup time): http://blog.2ndquadrant.com/incremental-backup-barman-1-4-0/ However, even smaller databases will benefit. It is clear that very small databases as well as frequently updated ones won't be interested in incremental backup, but that is never been the use case for this feature. I believe that if we still think that this approach is not worth it, we are making a big mistake. The way I see it, this patch follows an agile approach and it is an important step towards incremental backup on a block basis. > As Fujii says, if we ship this patch as written, people will just keep > using the timestamp-based approach anyway. I think that allowing users to be able to backup in an incremental way through streaming replication (even though based on files) will give more flexibility to system and database administrators for their disaster recovery solutions. Thanks, Gabriele
Re: [HACKERS] [RFC] Incremental backup v3: incremental PoC
Hi Marco, thank you for sending an updated patch. I am writing down a report of this initial (and partial) review. IMPORTANT: This patch is not complete, as stated by Marco. See the "Conclusions" section for my proposed TODO list. == Patch application I have been able to successfully apply your patch and compile it. Regression tests passed. == Initial run I have created a fresh new instance of PostgreSQL and activated streaming replication to be used by pg_basebackup. I have done a pgbench run with scale 100. I have taken a full consistent backup with pg_basebackup (in plain format): pg_basebackup -v -F p -D $BACKUPDIR/backup-$(date '+%s') -x I have been able to verify that the backup_profile is correctly placed in the destination PGDATA directory. Here is an excerpt: POSTGRESQL BACKUP PROFILE 1 START WAL LOCATION: 0/358 (file 00010003) CHECKPOINT LOCATION: 0/38C BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2015-01-14 10:07:07 CET LABEL: pg_basebackup base backup FILE LIST \N \N t 1421226427 206 backup_label \N \N t 1421225508 88 postgresql.auto.conf ... As suggested by Marco, I have manually taken the LSN from this file (next version must do this automatically). I have then executed pg_basebackup and activated the incremental feature by using the LSN from the previous backup, as follows: LSN=$(awk '/^START WAL/{print $4}' backup_profile) pg_basebackup -v -F p -D $BACKUPDIR/backup-$(date '+%s') -I $LSN -x The time taken by this operation has been much lower than the previous one and the size is much lower (I have not done any operation in the meantime): du -hs backup-1421226* 1,5Gbackup-1421226427 17M backup-1421226427 I have done some checks on the file system and then used the prototype of recovery script in Python written by Marco. ./recover.py backup-1421226427 backup-1421226427 new-data The cluster started successfully. I have then run a pg_dump of the pgbench database and were able to reload it on the initial cluster. == Conclusions The first run of this patch seems promising. While the discussion on the LSN map continues (which is mainly an optimisation of this patch), I would really like to see this patch progress as it would be a killer feature in several contexts (not in every context). Just in this period we are releasing file based incremental backup for Barman and customers using the alpha version are experiencing on average a deduplication ratio between 50% to 70%. This is for example an excerpt of "barman show-backup" from one of our customers (a daily saving of 550GB is not bad): Base backup information: Disk usage : 1.1 TiB (1.1 TiB with WALs) Incremental size : 564.6 GiB (-50.60%) ... My opinion, Marco, is that for version 5 of this patch, you: 1) update the information on the wiki (it is outdated - I know you have been busy with LSN map optimisation) 2) modify pg_basebackup in order to accept a directory (or tar file) and automatically detect the LSN from the backup profile 3) add the documentation regarding the backup profile and pg_basebackup Once we have all of this, we can continue trying the patch. Some unexplored paths are: * tablespace usage * tar format * performance impact (in both "read-only" and heavily updated contexts) * consistency checks I would then leave for version 6 the pg_restorebackup utility (unless you want to do everything at once). One limitation of the current recovery script is that it cannot accept multiple incremental backups (it just accepts three parameters: base backup, incremental backup and merge destination). Maybe you can change the syntax as follows: ./recover.py DESTINATION BACKUP_1 BACKUP_2 [BACKUP_3, ...] Thanks a lot for working on this. I am looking forward to continuing the review. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia - Managing Director PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it 2015-01-13 17:21 GMT+01:00 Marco Nenciarini : > Il 13/01/15 12:53, Gabriele Bartolini ha scritto: > > Hi Marco, > > > > could you please send an updated version the patch against the current > > HEAD in order to facilitate reviewers? > > > > Here is the updated patch for incremental file based backup. > > It is based on the current HEAD. > > I'm now working to the client tool to rebuild a full backup starting > from a file based incremental backup. > > Regards, > Marco > > -- > Marco Nenciarini - 2ndQuadrant Italy > PostgreSQL Training, Services and Support > marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it >
Re: [HACKERS] [RFC] Incremental backup v3: incremental PoC
Hi Marco, could you please send an updated version the patch against the current HEAD in order to facilitate reviewers? Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia - Managing Director PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it 2015-01-07 11:00 GMT+01:00 Marco Nenciarini : > Il 06/01/15 14:26, Robert Haas ha scritto: > > I suggest leaving this out altogether for the first version. I can > > think of three possible ways that we can determine which blocks need > > to be backed up. One, just read every block in the database and look > > at the LSN of each one. Two, maintain a cache of LSN information on a > > per-segment (or smaller) basis, as you suggest here. Three, scan the > > WAL generated since the incremental backup and summarize it into a > > list of blocks that need to be backed up. This last idea could either > > be done when the backup is requested, or it could be done as the WAL > > is generated and used to populate the LSN cache. In the long run, I > > think some variant of approach #3 is likely best, but in the short > > run, approach #1 (scan everything) is certainly easiest. While it > > doesn't optimize I/O, it still gives you the benefit of reducing the > > amount of data that needs to be transferred and stored, and that's not > > nothing. If we get that much working, we can improve things more > > later. > > > > Hi, > The patch now uses the approach #1, but I've just sent a patch that uses > the #2 approach. > > 54ad016e.9020...@2ndquadrant.it > > Regards, > Marco > > -- > Marco Nenciarini - 2ndQuadrant Italy > PostgreSQL Training, Services and Support > marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it > >
Re: [HACKERS] [RFC] Incremental backup v2: add backup profile to base backup
Hello, 2014-10-06 17:51 GMT+02:00 Marco Nenciarini : > I agree that a full backup does not need to include a profile. > > I've added the option to require the profile even for a full backup, as > it can be useful for backup softwares. We could remove the option and > build the profile only during incremental backups, if required. However, > I would avoid the needing to scan the whole backup to know the size of > the recovered data directory, hence the backup profile. > I really like this approach. I think we should leave users the ability to ship a profile file even in case of full backup (by default disabled). Thanks, Gabriele
Re: [HACKERS] Proposal: Incremental Backup
Hi Claudio, 2014-08-12 15:25 GMT+02:00 Claudio Freire : > Still not safe. Checksum collisions do happen, especially in big data sets. Can I ask you what you are currently using for backing up large data sets with Postgres? Thanks, Gabriele -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Incremental Backup
Hi Marco, > With the current full backup procedure they are backed up, so I think > that having them backed up with a rsync-like algorithm is what an user > would expect for an incremental backup. Exactly. I think a simple, flexible and robust method for file based incremental backup is all we need. I am confident it could be done for 9.5. I would like to quote every single word Simon said. Block level incremental backup (with Robert's proposal) is definitely the ultimate goal for effective and efficient physical backups. I see file level incremental backup as a very good "compromise", a sort of intermediate release which could nonetheless produce a lot of benefits to our user base, for years to come too. Thanks, Gabriele -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Incremental Backup
Hi Claudio, I think there has been a misunderstanding. I agree with you (and I think also Marco) that LSN is definitely a component to consider in this process. We will come up with an alternate proposal which considers LSNS either today or tomorrow. ;) Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it 2014-08-04 20:30 GMT+02:00 Claudio Freire : > On Mon, Aug 4, 2014 at 5:15 AM, Gabriele Bartolini > wrote: >>I really like the proposal of working on a block level incremental >> backup feature and the idea of considering LSN. However, I'd suggest >> to see block level as a second step and a goal to keep in mind while >> working on the first step. I believe that file-level incremental >> backup will bring a lot of benefits to our community and users anyway. > > Thing is, I don't see how the LSN method is that much harder than an > on-disk bitmap. In-memory bitmap IMO is just a recipe for disaster. > > Keeping a last-updated-LSN for each segment (or group of blocks) is > just as easy as keeping a bitmap, and far more flexible and robust. > > The complexity and cost of safely keeping the map up-to-date is what's > in question here, but as was pointed before, there's no really safe > alternative. Nor modification times nor checksums (nor in-memory > bitmaps IMV) are really safe enough for backups, so you really want to > use something like the LSN. It's extra work, but opens up a world of > possibilities. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Incremental Backup
Hi guys, sorry if I jump in the middle of the conversation. I have been reading with much interest all that's been said above. However, the goal of this patch is to give users another possibility while performing backups. Especially when large databases are in use. I really like the proposal of working on a block level incremental backup feature and the idea of considering LSN. However, I'd suggest to see block level as a second step and a goal to keep in mind while working on the first step. I believe that file-level incremental backup will bring a lot of benefits to our community and users anyway. I base this sentence on our daily experience. We have to honour (and the duty) to manage - probably - some of the largest Postgres databases in the world. We currently rely on rsync to copy database pages. Performing a full backup in 2 days instead of 9 days completely changes disaster recovery policies in a company. Or even 2 hours instead of 6. My 2 cents, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it 2014-08-01 19:05 GMT+02:00 Claudio Freire : > On Fri, Aug 1, 2014 at 1:43 PM, desmodemone wrote: >> >> >> >> 2014-08-01 18:20 GMT+02:00 Claudio Freire : >> >>> On Fri, Aug 1, 2014 at 12:35 AM, Amit Kapila >>> wrote: >>> >> c) the map is not crash safe by design, because it needs only for >>> >> incremental backup to track what blocks needs to be backuped, not for >>> >> consistency or recovery of the whole cluster, so it's not an heavy cost >>> >> for >>> >> the whole cluster to maintain it. we could think an option (but it's >>> >> heavy) >>> >> to write it at every flush on file to have crash-safe map, but I not >>> >> think >>> >> it's so usefull . I think it's acceptable, and probably it's better to >>> >> force >>> >> that, to say: "if your db will crash, you need a fullbackup ", >>> > >>> > I am not sure if your this assumption is right/acceptable, how can >>> > we say that in such a case users will be okay to have a fullbackup? >>> > In general, taking fullbackup is very heavy operation and we should >>> > try to avoid such a situation. >>> >>> >>> Besides, the one taking the backup (ie: script) may not be aware of >>> the need to take a full one. >>> >>> It's a bad design to allow broken backups at all, IMNSHO. >> >> >> Hi Claudio, >> thanks for your observation >> First: the case it's after a crash of a database, and it's not something >> happens every day or every week. It's something that happens in rare >> conditions, or almost my experience is so. If it happens very often probably >> there are other problems. > > Not so much. In this case, the software design isn't software-crash > safe, it's not that it's not hardware-crash safe. > > What I mean, is that an in-memory bitmap will also be out of sync if > you kill -9 (or if one of the backends is killed by the OOM), or if it > runs out of disk space too. > > Normally, a simple restart fixes it because pg will do crash recovery > just fine, but now the bitmap is out of sync, and further backups are > broken. It's not a situation I want to face unless there's a huge > reason to go for such design. > > If you make it so that the commit includes flipping the bitmap, it can > be done cleverly enough to avoid too much overhead (though it will > have some), and you now have it so that any to-be-touched block is now > part of the backup. You just apply all the bitmap changes in batch > after a checkpoint, before syncing to disk, and before erasing the WAL > segments. Simple, relatively efficient, and far more robust than an > in-memory thing. > > Still, it *can* double checkpoint I/O on the worst case, and it's not > an unfathomable case either. > >> Second: to avoid the problem to know if the db needed to have a full backup >> to rebuild the map we could think to write in the map header the backup >> reference (with an id and LSN reference for example ) so if the >> someone/something try to do an incremental backup after a crash, the map >> header will not have noone full backup listed [because it will be empty] , >> and automaticcaly switch to a full one. I think after a crash it's a good >> practice to do a full backup, to see if there are some problems on files or >> on filesystems, but if I am wrong I am happy to know :)
Re: [HACKERS] [PATCH] Support for pg_stat_archiver view
Hi Michael and Fujii, Il 01/02/14 17:46, Fujii Masao ha scritto: > I think that it's OK to add that as TODO item. There might be > the system that the speed of WAL archiving is slower than > that of WAL generation, at peak time. The DBA of that system > might want to monitor the size of archive queue. I agree that it is an interesting thing to do. The reason I didn't introduce it in the first place was that I did not want to make too many changes in this first attempt. > We can implement this by just counting the files with .ready > extension in pg_xlog/archive_status directory. Or we can also > implement that by adding new counter field in pg_stat_archiver > structure, incrementing it whenever creating .ready file, and > decrementing it whenever changing .ready to .done. I would love to give it a shot at the next opportunity. Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Relocation of tablespaces in pg_basebackup
Hi Steeve, Il 09/01/14 22:38, Steeve Lennmark ha scritto: > I'm a barman user myself so that was actually my initial thought. Ah! Very good! > If there aren't some kind of hidden internal that I've missed I don't see > a way to convert an OID (only have OID and path at this stage) to a > tablespace name. This solution, even though not optimal, is a lot > better than my initial one where I used the OID directly. Try: SELECT spcname, oid, pg_tablespace_location(oid) FROM pg_tablespace Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Relocation of tablespaces in pg_basebackup
Hi Steeve, > Il 09/01/14 22:10, Steeve Lennmark ha scritto: > > That's a much better solution, I attached a patch with the updated code. > > # SELECT oid, pg_tablespace_location(oid) FROM pg_tablespace; > [...] > 16388 | /tmp/tblspc1 > 16389 | /tmp/tblspc2 I'd suggest, a similar solution to the one we have adopted in Barman (if you don't know it: www.pgbarman.org), that is: --tablespace NAME:LOCATION [--tablespace NAME:location] I prefer this over the location on the master as this might change over time (at least more frequently than the tablespace name) and over servers. > $ pg_basebackup -Xs -D backup/data -T /tmp/tblspc1:$(pwd)/backup/t1 -T > /tmp/tblspc2:$(pwd)/backup/t2 With the above example, it would become: $ pg_basebackup -Xs -D backup/data -T tblspc1:$(pwd)/backup/t1 -T tblspc2:$(pwd)/backup/t2 Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for pg_stat_archiver view
Hi Fabrizio, Il 05/01/14 20:46, Fabrizio Mello ha scritto: > I don't see your code yet, but I would like to know if is possible to > implement this view as an extension. I wanted to do it as an extension - so that I could backport that to previous versions of Postgres. I do not think it is a possibility, given that the client code that is aware of the events lies in pgarch.c. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for pg_stat_archiver view
Il 05/01/14 13:52, Magnus Hagander ha scritto: > Yes, that's what I'm talking about. And just increment it by 1. Done. I am attaching version 2 of the patch, which now implements only one function (pg_stat_get_archiver()) and adds: * failed attempts * WAL of the last failed attempt * time of the last failed attempt Thanks for your inputs. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 4ec6981..0094c19 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -270,6 +270,14 @@ postgres: user database host + pg_stat_archiverpg_stat_archiver + One row only, showing statistics about the + WAL archiver process's activity. See +for details. + + + + pg_stat_bgwriterpg_stat_bgwriter One row only, showing statistics about the background writer process's activity. See @@ -648,6 +656,64 @@ postgres: user database host + + pg_stat_archiver View + + + + + Column + Type + Description + + + + + + archived_wals + bigint + Number of WAL files that have been successfully archived + + + last_archived_wal + text + Name of the last successfully archived WAL file + + + last_archived_wal_time + timestamp with time zone + Time of the last successful archival operation + + + failed_attempts + bigint + Number of failed attempts for archiving WAL files + + + last_failed_wal + text + Name of the WAL file of the last failed archival operation + + + last_failed_wal_time + timestamp with time zone + Time of the last failed archival operation + + + stats_reset + timestamp with time zone + Time at which these statistics were last reset + + + + + + + The pg_stat_archiver view will always have a + single row, containing data about the archiver process of the cluster. + + + pg_stat_bgwriter View @@ -1613,6 +1679,8 @@ postgres: user database host pg_stat_reset_shared('bgwriter') will zero all the counters shown in the pg_stat_bgwriter view. + Calling pg_stat_reset_shared('archiver') will zero all the + counters shown in the pg_stat_archiver view. diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 575a40f..5ea8c87 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -672,6 +672,17 @@ CREATE VIEW pg_stat_xact_user_functions AS WHERE P.prolang != 12 -- fast check to eliminate built-in functions AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL; +CREATE VIEW pg_stat_archiver AS +SELECT +s.archived_wals, +s.last_archived_wal, +s.last_archived_wal_time, +s.failed_attempts, +s.last_failed_wal, +s.last_failed_wal_time, +s.stats_reset +FROM pg_stat_get_archiver() s; + CREATE VIEW pg_stat_bgwriter AS SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, diff --git a/src/backend/postmaster/pgarch.c b/src/backend/postmaster/pgarch.c index 2bb572e..60f957c 100644 --- a/src/backend/postmaster/pgarch.c +++ b/src/backend/postmaster/pgarch.c @@ -36,6 +36,7 @@ #include "access/xlog_internal.h" #include "libpq/pqsignal.h" #include "miscadmin.h" +#include "pgstat.h" #include "postmaster/fork_process.h" #include "postmaster/pgarch.h" #include "postmaster/postmaster.h" @@ -46,6 +47,7 @@ #include "storage/pmsignal.h" #include "utils/guc.h" #include "utils/ps_status.h" +#include "utils/timestamp.h" /* -- @@ -647,6 +649,15 @@ pgarch_archiveXlog(char *xlog) snprintf(activitymsg, sizeof(activitymsg), "failed on %s", xlog); set_ps_display(activitymsg, false); + /* +* Send off activity statistics to the stats collector. +*/ + ArchiverStats.m_type = 'F'; + strncpy(ArchiverStats.m_xlog, + xlog, sizeof(ArchiverStats.m_xlog)); + ArchiverStats.m_timestamp = GetCurrentTimestamp(); + pgstat_send_archiver(); + return false; } ereport(DEBUG1, @@ -755,4 +766,12 @@ pgarch_archiveDone(char *xlog) (errcode_for_file_access(), errmsg("could not rename file \"%s\" to \"%s\": %m", rlogready,
Re: [HACKERS] [PATCH] Support for pg_stat_archiver view
Hi Magnus, Il 04/01/14 13:25, Magnus Hagander ha scritto: > My first reaction was that exactly those two things were missing. And > then I read your whole email :) :) > With those two, I think it would make much sense to have a view like > this. Ok, I will prepare version 2 with those. > I'd suggest making the view on top of an SRF like pg_stat_replication > and pg_stat_activity (for example), instead of a whole lot of separate > function calls like the older stats views. Ok, good idea. > in pgarch_ArchiveDone() you seem to be increasing the m_archived_vals > value for each call and then sending it off. And then you add that > number in the stats collector. Isn't that going to add the wrong > number in the end - after a while, the archiver is going to send "add > 100" when it's just sent one file? ISTM that pgstat_recv_archiver > should just do ++ on the value? You are right. The purpose was to set it to 1 in ArchiveDone (I might have missed that change), so that I can manage the failed counters in the same way. I will fix this in version 2. > Oh, and you need to change the format id number of the stats file. I have not found any instruction on how to set it. I assume you are talking about this: PGSTAT_FILE_FORMAT_ID0x01A5BC9B Any suggestion is welcome. > There's a quick review you for ;) I think it's definitely worthwhile > with those things fixed (and a proper review, that was just a quick > one-over) Thanks for that. It already means a lot if you agree too it is worth it. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Support for pg_stat_archiver view
Hello, please find attached the patch that adds basic support for the pg_stat_archiver system view, which allows users that have continuous archiving procedures in place to keep track of some important metrics and information. Currently, pg_stat_archiver displays: * archived_wals: number of successfully archived WAL files since start (or the last reset) * last_archived_wal: last successfully archived WAL file * last_archived_wal_time: timestamp of the latest successful WAL archival * stats_reset: time of last stats reset This is an example of output: postgres=# select * from pg_stat_archiver ; -[ RECORD 1 ]--+-- archived_wals | 1 last_archived_wal | 00010001 last_archived_wal_time | 2014-01-04 01:01:08.858648+01 stats_reset| 2014-01-04 00:59:25.895034+01 Similarly to pg_stat_bgwriter, it is possible to reset statistics just for this context, calling the pg_stat_reset_shared('archiver') function. The patch is here for discussion and has been prepared against HEAD. It includes also changes in the documentation and the rules.out test. I plan to add further information to the pg_stat_archiver view, including the number of failed attempts of archival and the WAL and timestamp of the latest failure. However, before proceeding, I'd like to get some feedback on this small patch as well as advice on possible regression tests to be added. Thank you. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 4ec6981..6d45972 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -270,6 +270,14 @@ postgres: user database host + pg_stat_archiverpg_stat_archiver + One row only, showing statistics about the + WAL archiver process's activity. See +for details. + + + + pg_stat_bgwriterpg_stat_bgwriter One row only, showing statistics about the background writer process's activity. See @@ -648,6 +656,49 @@ postgres: user database host + + pg_stat_archiver View + + + + + Column + Type + Description + + + + + + archived_wals + bigint + Number of WAL files that have been successfully archived + + + last_archived_wal + text + Name of the last successfully archived WAL file + + + last_archived_wal_time + timestamp with time zone + Time of the last successful archival operation + + + stats_reset + timestamp with time zone + Time at which these statistics were last reset + + + + + + + The pg_stat_archiver view will always have a + single row, containing data about the archiver process of the cluster. + + + pg_stat_bgwriter View @@ -1613,6 +1664,8 @@ postgres: user database host pg_stat_reset_shared('bgwriter') will zero all the counters shown in the pg_stat_bgwriter view. + Calling pg_stat_reset_shared('archiver') will zero all the + counters shown in the pg_stat_archiver view. diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 575a40f..3a8d7b4 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -672,6 +672,13 @@ CREATE VIEW pg_stat_xact_user_functions AS WHERE P.prolang != 12 -- fast check to eliminate built-in functions AND pg_stat_get_xact_function_calls(P.oid) IS NOT NULL; +CREATE VIEW pg_stat_archiver AS +SELECT +pg_stat_get_archiver_archived_wals() AS archived_wals, +pg_stat_get_archiver_last_archived_wal() AS last_archived_wal, +pg_stat_get_archiver_last_archived_wal_time() AS last_archived_wal_time, +pg_stat_get_archiver_stat_reset_time() AS stats_reset; + CREATE VIEW pg_stat_bgwriter AS SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, diff --git a/src/backend/postmaster/pgarch.c b/src/backend/postmaster/pgarch.c index 2bb572e..3cb1ddc 100644 --- a/src/backend/postmaster/pgarch.c +++ b/src/backend/postmaster/pgarch.c @@ -36,6 +36,7 @@ #include "access/xlog_internal.h" #include "libpq/pqsignal.h" #include "miscadmin.h" +#include "pgstat.h" #include "postmaster/fork_process.h" #include "postmaster/pgarch.h" #include "postmaster/postmaster.h" @@ -46,6 +47,7 @@ #include "storage/pmsignal.h" #include "utils/guc.h" #include "utils/ps_status.h" +#include "utils/timestamp.h" /* -- @@ -755,4 +757,12 @@ pgarch_archiveDone(char *xlog) (errcode_for_file
[HACKERS] Italian PGDay 2012, Call for papers is now open
The sixth edition of the Italian PostgreSQL Day (PGDay.IT 2012) will be held on November 23 in Prato, Tuscany. The International Call for Papers is now open. Talks and presentations in English are accepted. Information in English for papers submission is available at: http://2012.pgday.it/call-for-papers/international-call-for-papers/ For any kind of information, do not hesitate to contact the organising committee via email at pgday2...@itpug.org. Thank you. Cheers, Gabriele Gabriele Bartolini Italian PostgreSQL Users Group (ITPUG), President -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Il 30/07/12 19:16, Gabriele Bartolini ha scritto: And it can be also interchanged with "Array element Foreign Key". As promised, we have sent a patch for the "Array ELEMENT foreign key" support. We are discontinuing this thread here and continue discussing the former "Foreign Keys with Arrays"/"EACH Foreign Key" feature support from here: http://archives.postgresql.org/pgsql-hackers/2012-08/msg00011.php Thank you, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Il 30/07/12 19:11, Alvaro Herrera ha scritto: I was going to say the same, except I had ELEMENT as a capitalized word in my mind (and in the docs it'd be within ). So: "Array ELEMENT Foreign Key" +1 for me And it can be also interchanged with "Array element Foreign Key". -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hi guys, it is time to give another go to this patch. I would like to thank everyone for suggestions and ideas expressed through this list. We are happy that "Part 0" of the patch has been committed (array_remove() and array_replace() functions), which will be useful in "Part 2" (too early now to talk about it). Let's not rush though and focus on "Part 1" of the patch. :) First, I would like to find a unique and general term for this feature. We started with "Foreign keys with arrays" and ended up with "EACH foreign keys". Following Peter's suggestion, we will use the "ELEMENT" keyword (so that maybe in the future we can extend the usage). Our proposals are: * Array Foreign Key * Foreign Key Arrays * ELEMENT Foreign Keys * ... Which one is your favourite? Secondly, we have decided to split the patch we proposed back in March in two smaller patches. The most important goal of "Part 1" is to find a generally accepted syntax. By removing ACTION handling from "Part 1" (see limitations below), we believe that the community will be able to contribute more to driving future directions and requirements. Based on Peter's comments, we would like to propose the use of the "ELEMENT" keyword, rather than the "EACH" keyword proposed in March. You can find three examples at the bottom of this email. Finally, "Part 1" of this patch will have these limitations: * Only one |ELEMENT| column allowed in a multi-column key (same as the proposed patch in March) * Supported actions|: * NO ACTION|| * RESTRICT| Cheers, Gabriele Example 1: inline usage CREATE TABLE drivers ( driver_id integer PRIMARY KEY, first_name text, last_name text, ... ); CREATE TABLE races ( race_id integer PRIMARY KEY, title text, race_day DATE, ... final_positions integer[] ELEMENT REFERENCES drivers ); Example 2: with FOREIGN KEY CREATE TABLE races ( race_id integer PRIMARY KEY, title text, race_day DATE, ... final_positions integer[], FOREIGN KEY (ELEMENT final_positions) REFERENCES drivers ); Example 3: with ALTER TABLE CREATE TABLE races ( race_id integer PRIMARY KEY, title text, race_day DATE, ... final_positions integer[] ); ALTER TABLE races ADD FOREIGN KEY (ELEMENT final_positions) REFERENCES drivers; -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
Re: [HACKERS] Patch pg_is_in_backup()
Hello Gilles, thank you very much for your patience (and thank you Marco for supporting me). I apologise for the delay. I have retested the updated patch and it works fine with me. It is "ready for committer" for me. Cheers, Gabriele Il 14/06/12 11:36, Marco Nenciarini ha scritto: Hi Gilles, unfortunately Gabriele has been very busy recently and he asked me to check again the status of this patch for this commit fest. In order to speed up the application of the patch, I am sending an updated version which correctly compiles with current head. Gabriele will then proceed with the review. Thank you, Marco This body part will be downloaded on demand. -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hi Noah, Il 10/06/12 22:53, Noah Misch ha scritto: This has bitrotted; please refresh. Also, please evaluate Peter's feedback: http://archives.postgresql.org/message-id/1333693277.32606.9.ca...@vanquo.pezone.net Our goal is to work on this patch from the next commit fest. What we are about to do for this commit fest is to split the previous patch and send a small one just for the array_remove() and array_replace() functions. Then we will sit down and organise the development of the feature according to Peter's feedback. It is important indeed that we find a commonly accepted terminology and syntax for this feature. I hope this sounds like a reasonable plan. Thank you. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch pg_is_in_backup()
Hi Gilles, Sorry for the delay. Il 03/04/12 14:21, Gilles Darold ha scritto: +1, this is also my point of view. I have looked at the patch that contains both pg_is_in_backup() and pg_backup_start_time(). From a functional point of view it looks fine to me. I was thinking of adding the BackupInProgress() at the beginning of pg_backup_start_time(), but the AllocateFile() function already make sure the file exists. I have performed some basic testing of both functions and tried to inject invalid characters in the start time field of the backup_label file and it is handled (with an exception) by the server. Cool. I spotted though some formatting issues, in particular indentation and multi-line comments. Some rows are longer than 80 chars. Please resubmit with these cosmetic changes and it is fine with me. Thank you. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch pg_is_in_backup()
Hi Gilles, first and foremost, sorry for jumping in this thread so late. I read all previous discussions and I'd be happy to help you with this patch. Agreed and sorry for the response delay. I've attached 2 patches here, the first one is the same as before with just the renaming of the function into pg_is_in_exclusive_backup(). My quick response: I really like the idea of having a function that simply returns a boolean value. To be honest, I would have called it pg_is_in_backup() as you originally did - after all, I do not execute pg_start_exclusive_backup() or pg_stop_exclusive_backup(). It is more intuitive and pairs with pg_is_in_recovery(). I have never found any mention whatsoever in the documentation that talks about exclusive backup, and I am afraid it would generate confusion. However, I leave this up to the rest of the community's judgement (here is my opinion). I agree also that a function that returns the timestamp of the start of the backup might be useful. My opinion with the 'exclusive' keyword applies here too (I would simply name it pg_backup_start_time()). Finally, I tried to apply the patch but it looks like we need a new version that can apply with current HEAD. If you can do that, I am happy to assist with the review. Have a good weekend. Thank you, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hello Tom, I started to look at this patch a bit. I'm quite confused by the fact that some, but not all, of the possible FK action types now come in an EACH variant. This makes no sense at all to me. ISTM that EACH is a property of the FK constraint as a whole, that is that it says the constraint is from array elements on the referencing side to column values on the referenced side, rather than the normal case of column values to column values. The specification that Gianni posted applies only to v5 of the patch. The original idea was indeed to have the whole foreign key to be defined with an EACH property (initially we were actually thinking of the ARRAY keyword following your advice, then for grammar reasons we opted for EACH). However, during the actual development we faced some difficulties with multi-column foreign keys. Through discussions on this list and with the reviewer we opted to allow the EACH keyword at column level. We started with the case where at most one column is EACH, which is easier to understand. The case of two or more EACH columns in the same foreign key has been left open for future development. Why would the possible actions be affected, and why only these? We had to add the EACH variant to two actions (EACH CASCADE and EACH SET NULL), in order to leave users the flexibility to choose the operation to be performed in case of delete or update of one or more elements from the referenced table. Some users indeed might prefer that, in case a referenced row is deleted, the whole row is deleted (therefore they'd use the standard CASCADE action). Others mights simply require that references to that row is removed from the referencing array (therefore they'd use the variant EACH CASCADE action). The same concept applies for SET NULL (the whole array is set to NULL) and EACH SET NULL (referencing elements are set to NULL). Thank you. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hello, Il 15/03/12 05:03, Marco Nenciarini ha scritto: please find attached v4 of the EACH Foreign Key patch (formerly known also as Foreign Key Array). Please find attached version v4b which replaces v4 and fixes a bug in array_replace() and adds further regression tests on array_replace() and fixes a few typos in the documentation. Thank you, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it EACH-foreign-key.v4b.patch.bz2 Description: BZip2 compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hello Erik, Il 21/02/12 16:22, Erik Rijkers ha scritto: (I reply to an older message but I did use the newest patch, version 3) I wanted to have a look at v3 of this patch today, but it seems it won't apply and compile anymore. As Peter pointed out, it is due to a new Boolean field added in the pg_proc catalog. I have updated our patch to set this value to false by default. I'd like to try this out a bit; could you see if you can fix it? Thank you so much for dedicating your time on reviewing this patch. I have attached version 3b (noting that is just a very small modification/change to major 3 version of the patch), which passes all tests. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it EACH-foreign-key-constraints-aka-foreign-key-arrays.v3b.patch.bz2 Description: BZip2 compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dry-run mode for pg_archivecleanup
Ciao Alvaro, Il 06/02/12 16:02, Alvaro Herrera ha scritto: FWIW I committed this last week, though I changed the debug message wording slightly -- hope that's OK; it can be adjusted of course. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b2e431a4db81a735d1474c4d1565a20b835878c9 Beautiful! :) Thank you very much. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hi Noah, Il 21/01/12 21:42, Noah Misch ha scritto: On Sat, Jan 14, 2012 at 08:18:48PM +0100, Marco Nenciarini wrote: I greatly like that name; it would still make sense for other aggregate types, should we ever expand its use. Please complete the name change: the documentation, catalog entries, etc should all call them something like "each foreign key constraints" (I don't particularly like that exact wording). Ok, we'll go with "EACH Foreign Key Constraints" but I would allow the synonym "Foreign Key Array", especially in the documentation. How about: FOREIGN KEY(col_a, EACH col_b, col_c) REFERENCES pktable (a, b, c) We really like this syntax. However, as also Simon suggested, we'd go for switching to this syntax, but stick to a simpler implementation for 9.2. We will then be able to expand the functionality, by keeping the same syntax, from 9.3. To complete the ARRAY -> EACH transition, I would suggest names like CASCADE EACH/SET EACH NULL. Sounds perfect. Marco will go through all your comments and will send version 3 shortly. Thank you, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dry-run mode for pg_archivecleanup
Hi Alvaro, Il 27/01/12 23:55, Alvaro Herrera ha scritto: If you do that, please make sure you use two complete separate strings instead of building one from spare parts. This bit is missing the _() stuff though. Currently pg_archivecleanup does not comply with localisation standards. I can put this in the todo list however. Let me know what you think. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dry-run mode for pg_archivecleanup
Hi Robert, sorry for the delay. Il 27/01/12 15:47, Robert Haas ha scritto: This email thread seems to have trailed off without reaching a conclusion. The patch is marked as Waiting on Author in the CommitFest application, but I'm not sure that's accurate. Can we try to nail this down? Here is my final version which embeds comments from Josh. I have also added debug information to be printed in case '-d' is given. I will update the CommitFest website. Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it *** a/contrib/pg_archivecleanup/pg_archivecleanup.c --- b/contrib/pg_archivecleanup/pg_archivecleanup.c *** *** 36,41 const char *progname; --- 36,42 /* Options and defaults */ bool debug = false; /* are we debugging? */ + bool dryrun = false; /* are we performing a dry-run operation? */ char *archiveLocation;/* where to find the archive? */ char *restartWALFileName; /* the file from which we can restart restore */ *** *** 119,124 CleanupPriorWALFiles(void) --- 120,137 { snprintf(WALFilePath, MAXPGPATH, "%s/%s", archiveLocation, xlde->d_name); + + if (dryrun) + { + /* Prints the name of the file and +* skips the actual removal of the file */ + fprintf(stdout, "%s\n", WALFilePath); + if (debug) + fprintf(stderr, "%s: dry-run mode suggests removing file \"%s\"\n", + progname, WALFilePath); + continue; + } + if (debug) fprintf(stderr, "%s: removing file \"%s\"\n", progname, WALFilePath); *** *** 205,210 usage(void) --- 218,224 printf(" %s [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE\n", progname); printf("\nOptions:\n"); printf(" -d generates debug output (verbose mode)\n"); + printf(" -n shows the names of the files that would have been removed (dry-run)\n"); printf(" --help show this help, then exit\n"); printf(" --version output version information, then exit\n"); printf("\n" *** *** 241,253 main(int argc, char **argv) } } ! while ((c = getopt(argc, argv, "d")) != -1) { switch (c) { case 'd': /* Debug mode */ debug = true; break; default: fprintf(stderr, "Try \"%s --help\" for more information.\n", progname); exit(2); --- 255,270 } } ! while ((c = getopt(argc, argv, "dn")) != -1) { switch (c) { case 'd': /* Debug mode */ debug = true; break; + case 'n': /* Dry-Run mode */ + dryrun = true; + break; default: fprintf(stderr, "Try \"%s --help\" for more information.\n", progname); exit(2); *** a/doc/src/sgml/pgarchivecleanup.sgml --- b/doc/src/sgml/pgarchivecleanup.sgml *** *** 98,103 pg_archivecleanup: removing file "archive/00010037000E" --- 98,112 + + -n + + + Print the names of the files that would have been removed on stdout (performs a dry run). + + + + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dry-run mode for pg_archivecleanup
Hi Josh, Il 15/01/12 01:13, Josh Kupershmidt ha scritto: I have signed on to review this patch for the 2012-01 CF. The patch applies cleanly, includes the necessary documentation, and implements a useful feature. Thank you for dedicating your time to this matter. I think the actual debugging line: + fprintf(stdout, "%s\n", WALFilePath); My actual intention was to have the filename as output of the command, in order to easily "pipe" it to another script. Hence my first choice was to use the stdout channel, considering also that pg_archivecleanup in dry-run mode is harmless and does not touch the content of the directory. Oh, and I think the "removing file... " debug message above should not be printed in dryrun-mode, lest we confuse the admin. Yes, I agree with you here. Let me know what you think about my reasons for the stdout channel, then I will send v2. Thanks again! Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dry-run mode for pg_archivecleanup
Hi guys, I have added the '-n' option to pg_archivecleanup which performs a dry-run and outputs the names of the files to be removed to stdout (making possible to pass the list via pipe to another process). Please find attached the small patch. I submit it to the CommitFest. Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it *** a/contrib/pg_archivecleanup/pg_archivecleanup.c --- b/contrib/pg_archivecleanup/pg_archivecleanup.c *** *** 36,41 const char *progname; --- 36,42 /* Options and defaults */ bool debug = false; /* are we debugging? */ + bool dryrun = false; /* are we performing a dry-run operation? */ char *archiveLocation;/* where to find the archive? */ char *restartWALFileName; /* the file from which we can restart restore */ *** *** 123,128 CleanupPriorWALFiles(void) --- 124,136 fprintf(stderr, "%s: removing file \"%s\"\n", progname, WALFilePath); + if (dryrun) + { + /* Prints the name of the file and +* skips the actual removal of the file */ + fprintf(stdout, "%s\n", WALFilePath); + continue; + } rc = unlink(WALFilePath); if (rc != 0) { *** *** 205,210 usage(void) --- 213,219 printf(" %s [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE\n", progname); printf("\nOptions:\n"); printf(" -d generates debug output (verbose mode)\n"); + printf(" -n shows the names of the files that would have been removed (dry-run)\n"); printf(" --help show this help, then exit\n"); printf(" --version output version information, then exit\n"); printf("\n" *** *** 241,253 main(int argc, char **argv) } } ! while ((c = getopt(argc, argv, "d")) != -1) { switch (c) { case 'd': /* Debug mode */ debug = true; break; default: fprintf(stderr, "Try \"%s --help\" for more information.\n", progname); exit(2); --- 250,265 } } ! while ((c = getopt(argc, argv, "dn")) != -1) { switch (c) { case 'd': /* Debug mode */ debug = true; break; + case 'n': /* Dry-Run mode */ + dryrun = true; + break; default: fprintf(stderr, "Try \"%s --help\" for more information.\n", progname); exit(2); *** a/doc/src/sgml/pgarchivecleanup.sgml --- b/doc/src/sgml/pgarchivecleanup.sgml *** *** 98,103 pg_archivecleanup: removing file "archive/00010037000E" --- 98,112 + + -n + + + Print the names of the files that would have been removed on stdout (performs a dry run). + + + + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hi Noah, thanks for your feedback. Il 20/11/11 14:05, Noah Misch ha scritto: What about making ON UPDATE CASCADE an error? That way, we can say that ARRAY always applies to array elements, and plain always applies to entire rows. SET DEFAULT should now be fine to allow. It's ARRAY SET DEFAULT, in your new terminology, that wouldn't make sense. I have tried to gather your ideas with Gianni's and come to a compromise, which I hope you can both agree on. The reason why I would be inclined to leave CASCADE act on rows (rather than array elements as Gianni suggests) is for backward compatibility (people that are already using referential integrity based on array values). For the same reason, I am not sure whether we should raise an error on update, but will leave this for later. So, here is a summary: --- - - | ON| ON| Action | DELETE | UPDATE | --- - - CASCADE| Row | Error | SET NULL | Row | Row | SET DEFAULT| Row | Row | ARRAY CASCADE | Element | Element | ARRAY SET NULL | Element | Element | NO ACTION |-|-| RESTRICT |-|-| --- - - If that's fine with you guys, Marco and I will refactor the development based on these assumptions. Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Support for foreign keys with arrays
Hi Noah, thanks for your unvaluable review, rich of useful and thorough comments and notes. Marco and myself will add your proposed tests as soon as possible (most likely after the Italian PGDay which is this week). However, given the feedback received from other developers too (including Tom), I would first concentrate on defining the syntax and how referential integrity actions should work. Il 17/11/11 05:28, Noah Misch ha scritto: Removing values from the array seems best to me. There's no doubt about what ON UPDATE CASCADE should do, and having ON DELETE CASCADE excise individual array elements is consistent with that. It's less clear for SET NULL, but I'd continue with a per-element treatment. I'd continue to forbid SET DEFAULT. However, Jeff Davis did expect ON DELETE CASCADE to remove entire rows: http://archives.postgresql.org/message-id/1288119207.15279.24.camel@jdavis-ux.asterdata.local So, perhaps the behavior needs to be user-selectable. I would agree with what Tom is saying here, given that SQL specs do not say anything about this feature. We could leave standard REFERENCES keyword handling the array value as it is now. If a user wants to take advantage of in-array referential integrity, we could implement the special keyword "ARRAY REFERENCES" as Tom proposes (or a similar keyword). Consequently, we need to agree on what the actions on delete and update operations are. In case of ARRAY REFERENCES, I would be inclined to leave the same meaning of ROW scope actions to CASCADE and SET NULL actions, while disallowing the SET DEFAULT action (as Noah suggests too). At the same time, I would add two actions for ARRAY REFERENCES which will be processing array elements: * ARRAY CASCADE * ARRAY SET NULL (Of course if you are welcome to propose a better naming convention). This table summarises the scope of the actions. --- - - | ON| ON| Action | DELETE | UPDATE | --- - - CASCADE| Row | Element | SET NULL | Row | Row | ARRAY CASCADE | Element | Element | ARRAY SET NULL | Element | Element | SET DEFAULT| Error | Error | NO ACTION |-|-| RESTRICT |-|-| --- - - For instance, with an "ARRAY REFERENCES ... ON DELETE CASCADE", I would expect that the whole row is deleted (as Jeff et al. say). However, if I specify "ARRAY REFERENCES ... ON DELETE ARRAY CASCADE", I would expect that elements in the referencing array are removed. Similary the "ARRAY REFERENCES ... ON DELETE SET NULL" will set the row to NULL, whereas "ARRAY REFERENCES ... ON DELETE ARRAY SET NULL" will set individual elements in the referencing array to NULL. In case of updates, SET NULL and ARRAY SET NULL works the same (updating the whole row or the single elements). CASCADE and ARRAY CASCADE are synonyms, as they would work in individual elements (which is the action that makes more sense anyway). I believe that, before we proceed with one implementation or another, it is important we discuss this sort of things and agree on a possible long-term path (so that we can organise intermediate deliverables). Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Support for foreign keys with arrays
This patch adds basic support of arrays in foreign keys, by allowing to define a referencing column as an array of elements having the same type as the referenced column in the referenced table. Every NOT NULL element in the referencing array is matched against the referenced table. Example: CREATE TABLE pt ( id INTEGER PRIMARY KEY, ... ); CREATE TABLE ft ( id SERIAL PRIMARY KEY, pids INTEGER[] REFERENCES pt, ... ); This patch is for discussion and has been built against HEAD. It compiles and passes all regressions tests (including specific ones - see the src/test/regress/sql/foreign_key.sql file). Empty arrays, multi-dimensional arrays, duplicate elements and NULL values are allowed. We had to enforce some limitations, due to the lack (yet) of a clear and universally accepted behaviour and strategy. For example, consider the ON DELETE action on the above tables: in case of delete of a record in the 'pt' table, should we remove the whole row or just the values from the array? We hope we can start a discussion from here. Current limitations: * Only arrays of the same type as the primary key in the referenced table are supported * multi-column foreign keys are not supported (only single column) * Only RESTRICT and NO ACTION methods for referential integrity enforcement are currently supported TODO: * Improve check for empty arrays, which might interfere with SSI (see below) * Verify interaction with serializable transactions AUTHORS: * Gabriele Bartolini * Marco Nenciarini Cheers, Gabriele (and Marco) -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it *** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *** *** 764,769 CREATE TABLE order_items ( --- 764,796 the last table. + + Another option you have with foreign keys is to use a referencing column + which is an array of elements with the same type as the referenced column + in the related table. This feature, also known as foreign key arrays, + is described in the following example: + + + CREATE TABLE countries ( + country_id integer PRIMARY KEY, + name text, + ... + ); + + CREATE TABLE people ( + person_id integer PRIMARY KEY, + first_name text, + last_name text, + ... + citizenship_ids integer[] REFERENCES countries + ); + + + The above example lists in an array the citizenships held by + a person and enforces referential integrity checks. + + + CASCADE foreign key action *** *** 852,857 CREATE TABLE order_items ( --- 879,891 + When working with foreign key arrays, you are currently limited + to RESTRICT and NO ACTION + options, as the default behaviour for the other cases is not + clearly and universally determined yet. + + + More information about updating and deleting data is in . *** a/doc/src/sgml/ref/create_table.sgml --- b/doc/src/sgml/ref/create_table.sgml *** *** 576,581 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI --- 576,587 + If the referencing column is an array of elements of the same type as + the referenced column in the referenced table, the value of each element + of the array will be matched against some row of the referenced table. + + + A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH *** *** 634,640 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the ! referenced column, respectively. --- 640,647 Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the ! referenced column, respectively. Foreign key arrays are not ! supported by this action (as the behaviour is not easily determined). *** *** 643,649 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI SET NULL ! Set the referencing column(s) to null. --- 650,657 SET NULL ! Set the referencing column(s) to null. Foreign key arrays are not ! supported by this action (as the behaviour is not easily determined). *** *** 652,658 CREATE [ [ GLOBAL | LOCAL ] {
[HACKERS] Italian PGDay 2011, Call for papers is now open
The fifth edition of the Italian PostgreSQL Day (PGDay.IT 2011) will be held on November 25 in Prato. It will cover topics for users, developers and contributors to PostgreSQL, as well as decision and policy makers. For more information about the conference, please visit the website at http://2011.pgday.it/. Proposals for talks are now being accepted. Please note that talks will be accepted in both English and Italian. This call for papers is targeted for English speakers. Each session will last 30 minutes, and may be on any topic related to PostgreSQL. Suggested topic areas include: * Developing applications for PostgreSQL * Administering large scale PostgreSQL installations * Case studies of PostgreSQL deployments * PostgreSQL tools and utilities * PostgreSQL hacking * Community & user groups * Tuning the server * Migrating from other systems * Scaling/replication * Benchmarking & hardware * PostgreSQL related products Of course, proposals for talks on other PostgreSQL related topics will also be gladly accepted. A limited number of longer, 60-minute, slots will be available, especially aimed at practical tutorials. Please indicate clearly in your submission if you wish to make a 60-minute talk. It is also possible for you to give a tutorial on advanced use, administration or development of a PostgreSQL cluster. Every tutorial must have a live demo, didactic material for attendees and have to be no more long than 60 minutes. All the material required for the tutorial itself (e.g., slides, code snippets, howtos, etc.) are on the lecturer(s). Finally, there will be a session of five minute lightning talks. A separate call for proposals will be made for these further on. The submission deadline is October 16th, 2011. Selected speakers will be notified on October 18th, 2011. Please email your proposals to pgday2...@itpug.org, and include the following information: * Name * Company (if applicable) * Biography (a paragraph about you and your involvement with PostgreSQL) * Presentation title * Presentation abstract * Specific requests The proposals will be considered by a committee that will finalise the schedule to be published nearer the conference date. If your proposal has been accepted, you will be informed by email. Submission form and more detailed information are available as a PDF document here: http://2011.pgday.it/sites/default/files/PGDay2011-CFP-EN_0.pdf We look forward to hearing from you, and seeing you in Prato in November! Gabriele Bartolini Italian PostgreSQL Users Group (ITPUG), President
[HACKERS] Dry Run mode for pg_archivecleanup
Hi guys, I have added the '-n' option to pg_archivecleanup which performs a dry-run and outputs the names of the files to be removed to stdout (making possible to pass the list via pipe to another process). Please find attached the small patch. Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it >From 25fcf05ff787dae2b0d62de423a363f8597f1d42 Mon Sep 17 00:00:00 2001 From: Gabriele Bartolini Date: Mon, 27 Jun 2011 12:27:09 +0200 Subject: [PATCH] Add dryrun option to pg_archivecleanup --- contrib/pg_archivecleanup/pg_archivecleanup.c | 13 - doc/src/sgml/pgarchivecleanup.sgml|9 + 2 files changed, 21 insertions(+), 1 deletions(-) diff --git a/contrib/pg_archivecleanup/pg_archivecleanup.c b/contrib/pg_archivecleanup/pg_archivecleanup.c index dd8a451..0bbec92 100644 --- a/contrib/pg_archivecleanup/pg_archivecleanup.c +++ b/contrib/pg_archivecleanup/pg_archivecleanup.c @@ -36,6 +36,7 @@ const char *progname; /* Options and defaults */ bool debug = false; /* are we debugging? */ +bool dryrun = false; /* are we performing a dry-run operation? */ char *archiveLocation;/* where to find the archive? */ char *restartWALFileName; /* the file from which we can restart restore */ @@ -123,6 +124,12 @@ CleanupPriorWALFiles(void) fprintf(stderr, "%s: removing file \"%s\"\n", progname, WALFilePath); + if (dryrun) { + /* Prints the name of the file and +* skips the actual removal of the file */ + fprintf(stdout, "%s\n", WALFilePath); + continue; + } rc = unlink(WALFilePath); if (rc != 0) { @@ -205,6 +212,7 @@ usage(void) printf(" %s [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE\n", progname); printf("\nOptions:\n"); printf(" -d generates debug output (verbose mode)\n"); + printf(" -n shows the names of the files that would have been removed (dry-run)\n"); printf(" --help show this help, then exit\n"); printf(" --version output version information, then exit\n"); printf("\n" @@ -241,13 +249,16 @@ main(int argc, char **argv) } } - while ((c = getopt(argc, argv, "d")) != -1) + while ((c = getopt(argc, argv, "dn")) != -1) { switch (c) { case 'd': /* Debug mode */ debug = true; break; + case 'n': /* Dry-Run mode */ + dryrun = true; + break; default: fprintf(stderr, "Try \"%s --help\" for more information.\n", progname); exit(2); diff --git a/doc/src/sgml/pgarchivecleanup.sgml b/doc/src/sgml/pgarchivecleanup.sgml index ddffa32..8148c53 100644 --- a/doc/src/sgml/pgarchivecleanup.sgml +++ b/doc/src/sgml/pgarchivecleanup.sgml @@ -98,6 +98,15 @@ pg_archivecleanup: removing file "archive/00010037000E" + + -n + + +Print the names of the files that would have been removed on stdout (performs a dry run). + + + + -- 1.7.4.1 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed patch: Smooth replication during VACUUM FULL
Il 09/05/11 09:14, Simon Riggs ha scritto: Anyway, I hope I can give you more detailed information tomorrow. Thanks. Did you find anything else of note, or is your patch ready to commit? Unfortunately I did not have much time to run further tests. The ones I have done so far show that it mostly works (see attached graph), but there are some unresolved spikes that will require further work in 9.2. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it <> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed patch: Smooth replication during VACUUM FULL
Il 02/05/11 18:20, Simon Riggs ha scritto: I'm sure Gabriele can add those things as well - that also looks like another 1 line change. Yes, today we have performed some tests with that patch as well (attached is version 2). The version 2 of the patch (which includes the change Tom suggested on Saturday), smooths the process even more. You can look at the attached graph for now - even though we are currently relaunching a test with all 3 different versions from scratch (unpatched, patch v1 and patch v2), with larger data in order to confirm this behaviour. I'm just observing that the patch as-is appears effective and I feel it is important. Exactly. One thing also important to note as well is that with the vacuum delay being honoured, "vacuum full" operations in a SyncRep scenario take less time as well - as the load is more distributed over time. You can easily spot in the graphs the point where VACUUM FULL terminates, then it is just a matter of flushing the WAL delay for replication. Anyway, I hope I can give you more detailed information tomorrow. Thanks. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it <>diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index bcc7d1e..fa3d22f 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -894,7 +894,8 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, Buffer buf; boolisdead; - CHECK_FOR_INTERRUPTS(); + /* Launches vacuum delay */ + vacuum_delay_point(); if (indexScan != NULL) { @@ -1012,7 +1013,7 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, HeapTuple tuple; boolshouldfree; - CHECK_FOR_INTERRUPTS(); + vacuum_delay_point(); tuple = tuplesort_getheaptuple(tuplesort, true, &shouldfree); if (tuple == NULL) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposed patch: Smooth replication during VACUUM FULL
Hi guys, I have noticed that during VACUUM FULL on reasonably big tables, replication lag climbs. In order to smooth down the replication lag, I propose the attached patch which enables vacuum delay for VACUUM FULL. Please find attached the patch and below more information on this specific issue. Cheers, Gabriele == Scenario I have setup a simple SyncRep scenario with one master and one standby on the same server. On the master I have setup vacuum_cost_delay = 10 milliseconds. I have created a scale 50 pgbench database, which produces a 640MB pgbench_accounts table (about 82k pages). I have then launched a 60 seconds pgbench activity with 4 concurrent clients with the goal to make some changes to the pgbench table (approximately 1800 changes on my laptop). == Problem observed Replication lag climbs during VACUUM FULL. == Proposed change Enable vacuum delay for VACUUM FULL (and CLUSTER). == Test I have then launched a VACUUM FULL operation on the pgbench_accounts table and measured the lag in bytes every 5 seconds, by calculating the difference between the current location and the sent location. Here is a table with lag values. The first column (sec) is the sampling time (every 5 seconds for the sake of simplicity here), the second column (mlag) is the master lag on the current HEAD instance, the third column (mlagpatch) is the lag measured on the patched Postgres instance. sec | mlag | mlagpatch -+---+--- 0 | 1896424 |0 5 | 15654912 | 4055040 10 | 8019968 | 13893632 15 | 16850944 | 4177920 20 | 10969088 | 21102592 25 | 11468800 | 2277376 30 | 7995392 | 13893632 35 | 14811136 | 20660224 40 | 6127616 |0 45 | 6914048 | 5136384 50 | 5996544 | 13500416 55 | 14155776 | 9043968 60 | 23298048 | 11722752 65 | 15400960 | 18202624 70 | 17858560 | 28049408 75 | 8560640 | 34865152 80 | 19628032 | 33161216 85 | 25526272 | 39976960 90 | 23183360 | 23683072 95 | 23265280 | 303104 100 | 24346624 | 3710976 105 | 24813568 |0 110 | 32587776 | 7651328 115 | 42827776 | 12369920 120 | 50167808 | 14991360 125 | 60260352 | 3850240 130 | 62750720 | 5160960 135 | 68255744 | 9355264 140 | 60653568 | 14336000 145 | 68780032 | 16564224 150 | 74342400 | 5398528 155 | 84639744 | 11321344 160 | 92741632 | 16302080 165 | 70123520 | 20234240 170 | 13606912 | 23248896 175 | 20586496 | 29278208 180 | 16482304 | 1900544 185 |0 |0 As you can see, replication lag on HEAD's PostgreSQL reaches 92MB (160 seconds) before starting to decrease (when the operation terminates). The test result is consistent with the expected behaviour of cost-based vacuum delay. -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 191ef54..f10ae3c 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -877,7 +877,8 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, Buffer buf; boolisdead; - CHECK_FOR_INTERRUPTS(); + /* Launches vacuum delay */ + vacuum_delay_point(); if (indexScan != NULL) { -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
Il 04/04/11 22:26, Robert Haas ha scritto: I think you still need to update Solution.pm to match. Here it is, including change of 3 'Id' attributes (I made them lowercase). Thanks, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index bb5527c..9266558 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,6 +1,6 @@ - + Server Configuration diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index 7f515dc..144b880 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -1,6 +1,6 @@ - + Recovery Configuration diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml index 71ddc0f..f233e9d 100644 --- a/doc/src/sgml/typeconv.sgml +++ b/doc/src/sgml/typeconv.sgml @@ -1,6 +1,6 @@ - + Type Conversion diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm index e1fe4e9..1a7b94d 100644 --- a/src/tools/msvc/Solution.pm +++ b/src/tools/msvc/Solution.pm @@ -389,8 +389,8 @@ EOF open(O, ">doc/src/sgml/version.sgml") || croak "Could not write to version.sgml\n"; print O <{strver}"> -{majorver}"> +{strver}"> +{majorver}"> EOF close(O); } -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
Il 04/04/11 18:37, Tom Lane ha scritto: AFAIK, the main stumbling block for that is that XML doesn't allow abbreviated close tags (ie,whatever). Which is something that we are not likely to give up. So I'm not sure of the point of changing something as trivial as entity declaration casing. You're going to end up having to fork the documentation anyway, or at least feed it through an SGML to XML converter. So why not fix the entity casing then? We do it. However, given that the files that have been changed are edited very rarely, the scope of the patch is limited and compatibility with SGML is kept, I thought changing this at source level would be something to propose and submit. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Uppercase SGML entity declarations
Hi Robert (and Tom), Il 04/04/11 16:57, Robert Haas ha scritto: Forgive me for asking what may seem like a stupid question, but what's not XML compliant about them now, and why do we care? The text is only ever going to parse as SGML (not XML) so I guess I don't see why it matters. I don't really object to the proposed patch but I guess I'm not sure what it accomplishes. My apologies, I should have explained it better and at once. Here is the context. I am coordinating the Italian translation of the documentation and we are about to create a branch for 9.1. The publishing mechanism is based on DocBook XML (as well as the French one), as we are facing extreme difficulties publishing using SGML tools. My intention was to start and change some simple documentation files in order to make our conversion process from SGML to XML smoother, while keeping the SGML compatibility of the original documentation intact. Also I was trying to make the ENTITY declarations consistent throughout the documents. I hope I gave you a more clear explanation. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Uppercase SGML entity declarations
Hi, I have made very small modifications to a few files in the documentation directory, which involve SGML entity declarations. Currently they are all written lowercase, the patch makes them uppercase. This won't affect SGML parsing, as SGML is case insensitive - even though it is a good practice to write SGML declarations in uppercase. An example is: written This patch makes also every single directive of them XML compliant. The list of files that have been changed is: doc/src/sgml/Makefile doc/src/sgml/filelist.sgml doc/src/sgml/postgres.sgml doc/src/sgml/ref/allfiles.sgml doc/src/sgml/standalone-install.sgml Thanks. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it diff --git a/doc/src/sgml/Makefile b/doc/src/sgml/Makefile index 3041a53..c5078a8 100644 --- a/doc/src/sgml/Makefile +++ b/doc/src/sgml/Makefile @@ -126,8 +126,8 @@ bookindex.sgml: HTML.index # changes. version.sgml: $(top_srcdir)/configure { \ - echo ""; \ - echo ""; \ + echo ""; \ + echo ""; \ } > $@ features-supported.sgml: $(top_srcdir)/src/backend/catalog/sql_feature_packages.txt $(top_srcdir)/src/backend/catalog/sql_features.txt @@ -220,9 +220,9 @@ HISTORY.html: generate_history.pl $(wildcard $(srcdir)/release*.sgml) rm tempfile_HISTORY.sgml regress_README.html: regress.sgml - ( echo ''; \ - echo ' ]>'; \ + ( echo ''; \ + echo ' ]>'; \ cat $< ) >tempfile_regress_README.sgml $(JADE.text) -V nochunks tempfile_regress_README.sgml > $@ rm tempfile_regress_README.sgml diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 1a60796..ed39e0b 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -1,176 +1,176 @@ - - - - - - + + + + + + - - - + + + - + - - - - - - - - - - - - - + + + + + + + + + + + + + - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + - + %allfiles; - - - - - - - - - - - - - - + + + + + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + - - + + - - + + - + diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml index 98d19a5..ac1da22 100644 --- a/doc/src/sgml/postgres.sgml +++ b/doc/src/sgml/postgres.sgml @@ -2,12 +2,12 @@ + %version; - + %filelist; - + ]> diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index ac6ac5b..8a8616b 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -5,177 +5,177 @@ Complete list of usable sgml source files in this directory. --> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + diff --git a/doc/src/sgml/standalone-install.sgml b/doc/src/sgml/standalone-install.sgml index 87f9779..c94b397 100644 --- a/doc/src/sgml/standalone-install.sgml +++ b/doc/src/sgml/standalone-install.sgml @@ -21,7 +21,7 @@ Running 'make INSTALL' in the doc/src/sgml directory will do 1 through + %version; - - + +
[HACKERS] Italian PGDay 2010, Call for papers
The fourth edition of the Italian PostgreSQL Day (PGDay.IT 2010) will be held on December 10 in Rome. It will cover topics for users, developers and contributors to PostgreSQL, as well as decision and policy makers. For more information about the conference, please visit the website at http://2010.pgday.it/. Proposals for talks are now being accepted. Please note that talks will be accepted in both English and Italian. This call for papers is targeted for English speakers. Each session will last 30 minutes, and may be on any topic related to PostgreSQL. Suggested topic areas include: * Developing applications for PostgreSQL * Administering large scale PostgreSQL installations * Case studies of PostgreSQL deployments * PostgreSQL tools and utilities * PostgreSQL hacking * Community & user groups * Tuning the server * Migrating from other systems * Scaling/replication * Benchmarking & hardware * PostgreSQL related products Of course, proposals for talks on other PostgreSQL related topics will also be gladly accepted.A limited number of longer, 60-minute, slots will be available, especially aimed at practical tutorials. Please indicate clearly in your submission if you wish to make a 60-minute talk. It is also possible for you to give a tutorial on advanced use, administration or development of a PostgreSQL cluster. Every tutorial must have a live demo, didactic material for attendees and have to be no more long than 60 minutes. All the material required for the tutorial itself (e.g., slides, code snippets, howtos, etc.) are on the lecturer(s). Finally, there will be a session of five minute lightning talks. A separate call for proposals will be made for these further on. The submission deadline is October 24th, 2010. Selected speakers will be notified on October 27th, 2010. Please email your proposals to pgday2...@itpug.org <mailto:pgday2...@itpug.org>, and include the following information: * Name * Company (if applicable) * Biography (a paragraph about you and your involvement with PostgreSQL) * Presentation title * Presentation abstract * Specific requests The proposals will be considered by a committee that will finalise the schedule to be published nearer the conference date. If your proposal has been accepted, you will be informed by email. We look forward to hearing from you, and seeing you in Rome in December! Gabriele Bartolini Italian PostgreSQL Users Group (ITPUG), President
Re: [HACKERS] psycopg2 license changed
Ciao Federico, Federico Di Gregorio ha scritto: I just wanted all interested people know that psycopg2 2.0.14 to be released in the next few days will be under the LGPL3 + OpenSSL exception (example code and tests under the LGPL3 alone because they are never linked to OpenSSL). Thank you so much for your contribution! I hope this makes everybody happy, have fun, This is great news. I also want to point out that our valuable ITPUG member Daniele Varrazzo has started to write some documentation about PsycoPG2, which can be found here: http://initd.org/psycopg/docs/ It would be good if we could update our wiki as well in order to include this resource too. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Confusion over Python drivers
Hi there, Greg Smith ha scritto: Looks like the first action item is to talk with the Psycopg people about their license. Oh: and I'm going to take care of this. License changes can be a very sensitive topic and I'm told that discussion probably needs to happy in Italian too; I can arrange that. I can try and help with this issue, given my role with the Italian PostgreSQL community and PostgreSQL business with 2ndQuadrant Italia. I have met Psycopg's developer a couple of times at open-source conferences. I have great respect for his work and his contribution in the open-source community, and I will be very happy to try and explain the situation to him. I will keep you posted. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
I would personally emulate \d and take the chance for showing a funny warning, something like: "hey, it's not MySql!" or similar. I am sure we will Finder something appropriate. :) Inviato da iPhone Il giorno 20/gen/2010, alle ore 16.30, "Kevin Grittner" > ha scritto: Dimitri Fontaine wrote: I'll give my vote to Peter's idea that show tables; should better act as if you typed \d. I guess we don't need a "tables" GUC. Show all wouldn't include it? Would we require a semicolon? Do we support \d-style globs? Still seems kinda messy. +1 for help to show the PostgreSQL command as a guess for what they want to do. -1 for MySQL emulation. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mentors needed urgently for SoC & PostgreSQL Student Internships
Ciao Josh, Josh Berkus ha scritto: What this all hinges on is getting some really solid mentors who have projects they'd like students to work on, and can commit unconditionally to having 5 hours a week or more, over a 3-month period, to work with the student. Thanks for letting us know. However for this year we (as 2ndQuadrant) have just planned to collaborate with some Italian Universities, starting from the University of Pisa (I spoke to their IT students last Monday). I don't think we can dedicate more time to mentoring in the short period (that's a pity, I know). :( However, thanks again for keeping us informed. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers