Re: [ADMIN] [GENERAL] PostgreSQL Cache
On Mon, 29 Sep 2008, Matthew Pulis wrote: I need to perform some timed testing, thus need to make sure that disk cache does not affect me. Is clearing the OS (Ubuntu) disk cache, ( by running: sudo echo 3 | sudo tee /proc/sys/vm/drop_caches ) enough to do this? What you should do is: 1) Shutdown the database server (pg_ctl, sudo service postgresql stop, etc.) 2) sync 3) sudo echo 3 > /proc/sys/vm/drop_caches 4) Start the database server That will clear both the database and OS cache with a minimum of junk left behind in the process; clearing the cache without a sync is a bad idea. Note that all of this will still leave behind whatever cache is in your disk controller card or on the disk themselves available. There are some other techniques you could consider. Add a setp 2.5 that generates a bunch of data unused by the test, then sync again, and you've turned most of that into useless caching. Ideally, your test should be running against a large enough data set that the dozens or couple of hundred megabytes that might be in those will only add a bit of noise to whatever you're testing. If you're not running a larger test or going through tasts to make the caches clear, the only easy way to make things more clear is to reboot the whole server. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Virtualization vs. sharing a server
Michael Gould wrote: I don't know why virtualization is considered a no-no...Since these are all quad core with 32 gig running Windows 2003 64 bit, we can run about 100 users concurrently on each application server before we start to see a strain. You answered your own question here. Ramiro is looking for suggestions for how to scale up to >500 connections at once, and it's not that likely virtualization can fill any useful role in that context. If you're happy with 100, sure you can deploy on VMware ESX and have that work. There are performance vs. manageability tradeoffs when deciding if virtualized deployment makes sense, and for smaller workloads it's easy to dismiss the performance side of things as not a limiting factor and therefore favor VMs. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Virtualization vs. sharing a server
Ramiro Barreca wrote: 1. Is there a configuration option we need to consider to share this server? The two main configuration options that impact how much RAM PostgreSQL uses are shared_buffers and work_mem. If the server is shared, you just need to avoid tuning those upwards as far as you would on a dedicated system. The defaults for both are tiny, so unless you've already pushed these upwards a lot you're unlikely to have any issues in an initial deployment. 1. What is the recomended hardware for a PostgreSQL server that has to support between 500 and 1000 simultaneous user connections? Is there a whitepaper about this? The recommendation is "try not to do that". PostgreSQL connections are fairly expensive, and scaling into hundreds of connections on a single system tends to break down due to connection creation/teardown overhead. You should be investigating a connection pooler to put in between the application and the database, limiting the number of connections to something much smaller. PgBouncer is most people's first choice for this scale of database: http://pgfoundry.org/projects/pgbouncer Once you've gotten past that issue, recommended hardware varies greatly depending on workload. Read vs. write balance? Size of the critical working set in RAM? Size of the database? Peak commit rate? Types of queries executed by the application? It all factors into the decision. Add in business requirements for reliability and the inevitable vendor preference, you're likely to end up with a stack of possible configurations you could consider to sort through. There is no generic recommendation for larger systems. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Re: [PERFORM] Database size growing over time and leads to performance impact
Please don't cc two of the lists here. It makes things difficult for users who only subscribe to one list or the other who reply--their post to the other list will be held for moderation. And that's a pain for the moderators too. In this case, either the pgsql-admin or pgsql-performance list would have been appropriate for this question, but not both at the same time. The suggested approach when unsure is to try the most obvious list, and if you don't get a response after a day or two then try a second one. Gnanakumar wrote: We're using PostgreSQL 8.2. Recently, in our production database, there was a severe performance impact.. Even though, we're regularly doing both: 1. VACUUM FULL ANALYZE once in a week during low-usage time and 2. ANALYZE everyday at low-usage time Also, we noticed that the physical database size has grown upto 30 GB. But, if I dump the database in the form of SQL and import it locally in my machine, it was only 3.2 GB. Most VACUUM problems are caused by not running VACUUM often enough. A weekly VACUUM is really infrequent. And it's rarely ever a good idea to run VACUUM FULL. You should switch over to running a regular VACUUM, not a full one, on something closer to a daily or more frequent basis instead. Then while searching in Google to optimize database size, I found the following useful link: http://www.linuxinsight.com/optimize_postgresql_database_size.html It says that even vacuumdb or reindexdb doesn't really compact database size, only dump/restore does because of MVCC architecture feature in PostgreSQL and this has been proven here. That article covers PostgreSQL as of V7.4, and much of it is outdated information that doesn't apply to the 8.2 you're running. It's a pretty bad description even of that version. You should try to forget everything you read there and instead look at http://www.postgresql.org/docs/8.2/interactive/maintenance.html for an accurate introduction to this topic. I'm sorry you've been misled by it. Physical database size was found using the following command: du -sh /usr/local/pgsql/data/base/ I also cross-checked this size using "pg_size_pretty(pg_database_size(datname))". You should use the queries shown at http://wiki.postgresql.org/wiki/Disk_Usage instead of this, which will break down where the disk space is going by table and index. You will discover one of two things: 1) As the database grows, most of the disk space is being taken up by the tables themselves. In this case, a more frequent VACUUM is likely to make that go away. You might also need to bump up one of the parameters in the postgresql.conf file, max_fsm_pages 2) Lots of disk space is being taken up by indexes on the tables. If this is the case, the fact that you're running VACUUM FULL all the time is the likely cause of your problem. Questions 1. Is there any version/update of PostgreSQL addressing this issue? 2. How in real time, this issues are handled by other PostgreSQL users without taking to downtime? 3. Any ideas or links whether this is addressed in upcoming PostgreSQL version 9.0 release? PostgreSQL 8.3 turns on a better tuned autovacuum by default so that it's more likely VACUUM will run often enough to keep the problem you're having from happening. 8.4 removes an additional source of problems that can cause VACUUM to stop working. As of 8.4, most of the problems in this area are gone in the default configuration. Just looking at newer versions of the associated documentation will give you an idea what's changed; http://www.postgresql.org/docs/current/interactive/maintenance.html is the 8.4 version. The problems with VACUUM FULL are so bad that as of 9.0, the old implementation of that (the one you're probably getting bad behavior from) has been replaced by a more efficient one. The main situation newer PostgreSQL versions can still run into a problem where the indexes get large if you're deleting records in some ways; http://www.postgresql.org/docs/8.2/static/routine-reindex.html describes that issue, and that bit of documentation and the underlying behavior is unchanged in later releases. It's much more likely that you're running into the very common situation instead where you're running VACUUM FULL infrequently, where you should be running regular VACUUM frequently instead. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Migrate postgres to newer hardware
Renato Oliveira wrote: Are there any commercial solutions out there for migrating large DBs? I'm not aware of any. The main way to address this problem by throwing money at it is to hire someone extremely familiar with PostgreSQL replication technology and figure out how to customize one of the available approaches (Slony, Londiste, PITR, dump/restore) to match your application. For example, in some cases it's possible to record database changes on the application side, replicate the database via one of the fast online approaches like PITR, and then synchronize just the changes made in the master while that was happening for a fast switch-over to a new version. It's not unheard for that to require small application changes to support, to provide an easier way to log the difference between the two. If you can't take the additional load of Slony and have minimal tolerance for downtime, you really need to come up with a long-term approach to coping with that from an application architecture perspective. Unfortunately you're not going to find any quick fix for that combination of requirements. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Virtualization vs. sharing a server
Rodger Donaldson wrote: On Tue, March 30, 2010 06:09, Greg Smith wrote: You answered your own question here. Ramiro is looking for suggestions for how to scale up to >500 connections at once, and it's not that likely virtualization can fill any useful role in that context. That rather depends on your virtualisation layer. We haven't run large PG databases on our zLinux/zVM machines, but we have Oracle DBs running comparable connection numbers without any issues. Connection scaling in Oracle doesn't have the same characteristics as PostgreSQL, so you can't extrapolate from that. My point was that the connection target here would be aggressive and difficult to achieve even without virtualization involved. The virtualization sofware used will impact the exact percentage of overhead involved, but you'd be hard pressed to get this to work as hoped even if that number were 0--so anything >0, even small, is increasing the odds of failure. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Any good olap benchmarks?
Vladimir Rusinov wrote: I'm searching for any good OLAP-like benchmark: I need some benchmark with complex selects run on 10-30G dataset, something like this: http://www.percona.com/docs/wiki/benchmark:wikistat:start, but this is only a draft and would only be released for mysql. I already intended to convert and run Vadim's Wikipedia statistics benchmark when I get to it; as we're busy getting the next PostgreSQL release out the door right now I just haven't gotten to it yet. I expect we can get that added into their mix on the same hardware once I'm done. I've linked to everything I'm aware of at http://wiki.postgresql.org/wiki/Category:Benchmarking and the TPC-H page has probably the most relevant information for OLAP. PostgreSQL doesn't do particularly well on OLAP benchmarks yet due to how queries are limited by a single processor, making it hard to get excited about publishing the results. Greenplum ran some interesting tests of their own software against PostgreSQL at http://community.greenplum.com/showthread.php?t=113 you might find interesting. That includes a fairly easy to use TPC-H like test kit program, and by showing where they did much better is suggest the areas that community Postgres struggles relative to software that handles parallel query across multiple cores/servers. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Migrate postgres to newer hardware
Renato Oliveira wrote: I am going to gather the figures about our database and I will email to the list, if I am allowed to. Number of tables, number of transactions per day etc. A quick snapshot from "vmstat 1" during a busy time is also very helpful. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] turn pitr 'on' on PostgreSQL 8.2 - pg_standby
Renato Oliveira wrote: What is the difference between: archive_command = on (is this command for 8.2?) |archive_mode = on (This one is for 8.3!)| 8.2 doesn't have a archive_mode setting, that was added in 8.3. With 8.2, you set the archive_command in the same way as you do in 8.3. There just isn't a dedicated parameters to toggle archiving on and off that's separate from changing the command used. |In order to bring the standby online I need a trigger file, something like:| |can I call the trigger file whatever I want? for example; ‘/var/log/postgres/postgresql.8.2.trigger’| It doesn't matter where the trigger file is at so long as you use it correctly. The other thing to be careful of, that you already noted, is that since 8.2 doesn't ship with pg_standby, you need to grab your version of that. You should try to use the one that ships with 8.4. Also, it's not going to support the "%r" syntax in the restore_command, so you need to set the "-k" setting to a moderately high value for 8.2 servers. It's difficult to say exactly how high that must be for any possible workload. Something like "-k 256" is a reasonable starter setting that will save the last 4GB of archive logs shipped over on the standby, which is high enough to make deleting one of them prematurely unlikely (but not impossible for the right difficult workload). -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Getting the initdb parameter values
Satish Burnwal (sburnwal) wrote: I have an existing postgres database and I want to know what are the values of the parameters that were determined during initdb. Is there any way to know those values ? I normally use: select name,current_setting(name) from pg_settings where source='configuration file' To figure out all the things that were set at initdb time from a new running database. You can also look at the postgresql.conf file it generated and search for lines that are not commented out with "#" at the start. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"
Alexandre Leclerc wrote: - 2. Could we stop VACUUM FULL and simply restart postmaster and starting a normal VACUUM even if it's slow? This is what you want to do. VACUUM FULL is the slow way--much, much slower--and it is not needed to clean up from wraparound issues. Here's two more opinions on that: http://archives.postgresql.org/message-id/5224.1255142...@sss.pgh.pa.us And here's some notes about why you should avoid VACUUM FULL: http://wiki.postgresql.org/wiki/VACUUM_FULL (Note that you don't have a good CLUSTER implementation in your 8.1 server, so using that to clean up tables isn't an option) -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Vacuum Full (PG 8.1) - Urgent help needed - Cancel & transaction "liberation"
Kevin Grittner wrote: Also, the "full-database vacuum" terminology seems too likely to be interpreted as VACUUM FULL for best results. Perhaps it's worth changing that to just "database vacuum" or "vacuum of the entire database" http://archives.postgresql.org/pgsql-committers/2008-12/msg00096.php It's "database-wide" now instead of "full-database". -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres 8.5 partitioning
Anj Adu wrote: Will 8.5 have table partitions as first-class objects (i.e not inheritted tables..but partitions) What was originally going to be 8.5 was renumbered to be 9.0. None of the planned partitioning improvements are included in it. The changes planned there aren't a large as you seem to think they are though. Partitions will still be inherited tables, the improvements are just going to reduce the amount of setup/trigger code you need to write and make management tasks easier. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] more 10K disks or less 15K disks
Anj Adu wrote: I am faced with a hardware choice for a postgres data warehouse (extremely high volume inserts..over 200 million records a day) That's an average of 2314 per second, which certainly isn't easy to pull off. You suggested you're already running this app. Do you have any idea how high the volume of data going to the WAL is relative to everything else? 12 x 600G disks (15K) (the new Dell Poweredge C server) or 24 x 600G (10K disks) You can expect the 15K disks to be 30% (more sequential work) to 50% (random/commit work) faster than a similar 10K drive. So from most perspectives, twice as many 10K drives should be considerably faster. The main point of concern here is the commit rate, which you can't necessarily improve just by throwing drives at it. That's based on how fast the drives spin, so there's the potential to discover a 50% regression there compared to the setup you have right now. With the RAID card in there, it should be fine, but it's something to be concerned about. Also, you didn't mentioned the RAID card for the new system, and whether it would be the same in both setups or not. That can be as important as the drives when you have larger arrays. The LSI Megaraid card Dell is using for the Perc6i is quite fast, and you'll need to make sure you get something just as good for the new server. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] more 10K disks or less 15K disks
Anj Adu wrote: We do not archive the WALs. We use application-level replication to achieve redundancy. WAL archiving was difficult to support with the earlier hardware we had ( 6x300G 10K disks Dell 2850) given the volumes we were dealing with. The RAID card should be from the same manufacturer (LSI in Dell's case). The database is generating WAL files that are written to the pg_xlog directory. Sometimes this is broken out into a separate drive so that it's possible to measure how much I/O is being written to there, as opposed to the main database drive. That's the WAL writing I was asking about, not the overhead of archiving WAL files to elsewhere. The way that WAL writes go to disk, you can't always speed them up just by throwing more disks at them--sometimes, you just need the individual disk involved to be as fast as possible. You should try to get the same Dell RAID controller you're already using, that you know delivers good performance running your app. All I've heard about the models released after the Perc 6i has been bad news. Dell varies how much they tinker with the LSI firmware in their own version of each card, and they didn't do very much of that in the Perc 6 series. They seem to be changing around the newer models more again, which is always bad news. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] more 10K disks or less 15K disks
Scott Whitney wrote: On the 10k vs 15k rpm disks, there's a _lot_ to be said about that. I don't want to start a flame war here, but 15k versus 10k rpm hard drives does NOT equivocate to a 50% increase in read/write times, to say the VERY least. Your characterization is correct were there only the drives involved here, so no flames on your raw data. Once you've introduced a battery-backed write cache into the mix, this whole area becomes impossible to compute that way though, and it was that context I was commenting from at least. Those are good at turning random I/O into something more like sequential, as well as reducing the number of times you pay for rotational latency in several common database operations. The effective impact is to significantly narrow the difference between drives where the seek and rotation time are the main differences in a database context--even though the worst-case IOPS doesn't really change. IOPS is an interesting number to compute, but real-world database performance isn't linearly correlated with it. Maybe if your workload consists mainly of random, uncached index scans system performance will scale just like IOPS, but that's pretty uncommon. [Rant about making sure not to drink the storage vendor IOPS Kool-Aid deleted] -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Downgrading v8.4 database to v8.3
Jason Tan Boon Teck wrote: I need to move some databases written in Psql v8.4 to the stable production server running v8.3. I tried pg_dump and pg_restore as well as PgAdmin3. I am unable to do so due to the backward incompatibility. Is there anyway to do this? You can try connecting a 8.3 client to the 8.4 server, then running pg_dump from that 8.3 client. That's a common approach for dump/restore when moving forward a version, and it may resolve your issue when moving backward one too. The 8.3 client shouldn't dump anything the 8.3 server doesn't know how to restore. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Downgrading v8.4 database to v8.3
Tom Lane wrote: That's very likely to fail, and worse to do so silently, because 8.3 pg_dump doesn't know what's different about 8.4 system catalogs. I think your only real recourse in this situation is to do a plain dump from the 8.4 server (with 8.4 pg_dump) and then manually edit the dump script to remove any 8.4-only syntax. I tried not to sound optimistic about it working. Thought it might be worth a shot though, on the possibility that a simple schema might not trip over any of the 8.4 catalog changes. Another idea here, along the path of manually editing dumps, is to dump from both 8.3 and 8.4 clients and look at the difference between the two, to help get an idea what changed syntax is responsible for the problems. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Asynchronous commit | Transaction loss at server crash
Jesper Krogh wrote: A Battery Backed raid controller is not that expensive. (in the range of 1 or 2 SSD disks). And it is (more or less) a silverbullet to the task you describe. Maybe even less; in order to get a SSD that's reliable at all in terms of good crash recovery, you have buy a fairly expensive one. Also, and this is really important, you really don't want to deploy onto a single SSD and put critical system files there. Their failure rates are not that low. You need to put them into a RAID-1 setup and budget for two of them, which brings you right back to Also, it's questionable whether a SSD is even going to be faster than standard disks for the sequential WAL writes anyway, once a non-volatile write cache is available. Sequential writes to SSD are the area where the gap in performance between them and spinning disks is the smallest. Plugging your system (SSD's) with an UPS and trusting it fully could solve most of the problems (running in writeback mode). UPS batteries fail, and people accidentally knock out over server power cords. It's a pretty bad server that can't survive someone tripping over the cord while it's busy, and that's the situation the "use a UPS" idea doesn't improve. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Asynchronous commit | Transaction loss at server crash
Balkrishna Sharma wrote: I need to support several hundreds of concurrent update/inserts from an online form with pretty low latency (maybe couple of milliseconds at max). Think of a save to database at every 'tab-out' in an online form. I regularly see 2000 - 4000 small write transactions per second on systems with a battery-backed write cache and a moderate disk array attached. 2000 TPS = 0.5 ms, on average. Note however that it's extremely difficult to bound the worst-case behavior possible here anywhere near that tight. Under a benchmark load I can normally get even an extremely tuned Linux configuration to occasionally pause for 1-3 seconds at commit time, when the OS write cache is full, a checkpoint is finishing, and the client doing the commit is stuck waiting for that. They're rare but you should expect to see that situation sometimes. We know basically what causes that and how to make it less likely to happen in a real application. But the possibility is still there, and if your design cannot tolerate an occasional latency uptick you may be disappointed because that's very, very hard to guarantee with the workload you're expecting here. There are plenty of ideas for how to tune in that direction both at the source code level and by carefully selecting the OS/filesystem combination used, but that's not a very well explored territory. The checkpoint design in the database has known weaknesses in this particular area, and they're impossible to solve just by throwing hardware at the problem. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Relation between RAM / shmmax / shmall / shared_buffers
Balkrishna Sharma wrote: 1. Keeping the kernel parameter kernel.shmmax at 75% of RAM (i.e. at 3GB )kernel.shmmax = 3221225472 2. Keeping the kernel parameter shmall at the same value. Because shmall is measured in number of pages and each page on my linux is 4096 bytes, having kernel.shmall = 786432 (786432 * 4096 = 3221225472, same as shmmax) There's little reason to put shmmax at over 50% of RAM, because shared_buffers is going to be significantly lower than that even. I use the attached script for this job now; I got sick of doing the math manually all the time. If you're on a system that supports returning memory info using getconf, it outputs the lines you need to put into the kernel configuration. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us #!/bin/bash # Output lines suitable for sysctl configuration based # on total amount of RAM on the system. The output # will allow up to 50% of physical memory to be allocated # into shared memory. # On Linux, you can use it as follows (as root): # # ./shmsetup >> /etc/sysctl.conf # sysctl -p # Early FreeBSD versions do not support the sysconf interface # used here. The exact version where this works hasn't # been confirmed yet. page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` if [ -z "$page_size" ]; then echo Error: cannot determine page size exit 1 fi if [ -z "$phys_pages" ]; then echo Error: cannot determine number of memory pages exit 2 fi shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo \# Maximum shared segment size in bytes echo kernel.shmmax = $shmmax echo \# Maximum number of shared memory segments in pages echo kernel.shmall = $shmall -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] What is field separator?
Tena Sakai wrote: $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \ > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket What's denoted as \t is not a tab character. It is a two character sequence backslash followd by a t. I have tried all other possibilities such as \\t , "\t", etc that I can think of, but I cannot get an honest to god tab character to appear as field separator. Yeah, that's a tough one. In bash you can use ANSI C quoting to pull this off: psql -c "select name,setting from pg_settings limit 1" -d postgres -At -F $'\t' See http://wiki.bash-hackers.org/syntax/quoting for more information. Portability to other shells I'm not sure about. Note that I did two other things a little differently than your example, as well as tightening up the abbreviations: -Putting the command in -c " " is just better all around than piping into psql using echo. It even works with multi-line input, i.e.: psql -c " select 1 " -Explicitly specifying the database manually using -d makes what's happening easier to follow than expecting people to know the convention that the first non-option passed to psql is a database name. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] What is field separator?
Tena Sakai wrote: I got it to work by emulating what you wrote. But I think you meant -A, not -At below. psql -c "select name,setting from pg_settings limit 1" -d postgres -At -F $'\t' You had -t in your original to turn off the display of column names and the counts at the end, and -A is the shortcut for what you had as "-P 'format=unaligned'". I throw "-At" into almost every use of psql from a bash script I do, that's the usual combination that gets the basic format to be right; then tweak things like the field separator afterwards. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] graceful reload
Vasiliy G Tolstov wrote: In config file some in of this settings exists comment line like "changes require restart". This is mean exactly restart or reload can do this? Run this: $ psql -c "select name,context from pg_settings" This will sort out which settings are in which category definitively, rather than relying on the sometimes ambiguous comments in the file. Settings that say "postmaster" require a full database restart. Ones labeled "sighup" can be changed in the postgresql.conf and then the server signaled to use them, most easily with: pg_ctl reload -OR- psql -c "select pg_reload_conf()" Ones labeled "user" you can actually change for any user at any time during their session. Modifying the defaults and then executing the sighup procedure will make those new defaults active, but not for any clients that already have open connections. I recommend using SHOW or looking at pg_settings after making a change and reloading the server config to confirm it took. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Details about pg_stat_bgwriter
Thomas Kellerer wrote: If I want to identify a possible bottleneck with the bgwriter, for what kind of numbers do I need to watch out? You don't much with a single snapshot of pg_stat_bgwriter data. Try saving this instead: select *,now() from pg_stat_bgwriter; And then take another snapshot at least a few hours later, preferably the next day. With two snapshots and timestamps on them, then it's possible to make some sense of the numbers. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Details about pg_stat_bgwriter
Greg Smith wrote: You don't much with a single snapshot of pg_stat_bgwriter data. Try saving this instead: select *,now() from pg_stat_bgwriter; And then take another snapshot at least a few hours later, preferably the next day. With two snapshots and timestamps on them, then it's possible to make some sense of the numbers. I probably should have explained the next part. I've now shared what I do with this information at http://www.pgcon.org/2010/schedule/events/218.en.html Basically, if you put the data from the two snapshots into one of the Statistics Spreadsheet versions, you'll get several derived numbers that pop out: -Average checkpoint frequency -Average size of each checkpoint -Average rate at which new buffers are allocated -Average rate of writes out of the buffer cache -Percentage of writes done by checkpoints, the background writer LRU cleaner, and client backends These are the sort of things you can actually think about in useful real-world terms. And if you tune the database by doing things like increasing checkpoint_segments/checkpoint_timeout or changing the size of shared_buffers, you can see what impact that has on the overall system response, by noting how it changes these numeric measures. It's a bit more exact of an approach for tuning such things than what people have traditionally done here. Increasing shared_buffers and watching the total writes/second number drop significantly is more exact than just nothing whether the server waiting for I/O percentage dropped or not. I also highly recommend installing pg_buffercache and looking at what's inside your buffer cache too--the data actually being cached by the server never fails to surprise people, relative to what they expected was being cached. Source for how to analyze those things is in the pg_buffercache samples shell script, the "Buffer contents summary, with percentages" is the one that gives useful output. One of the thing I did in the presentation that does not come through on the slides is note how the distribution of high usage count data in your buffer cache determines whether you will benefit from making it large or not. I've reduced this to a single figure of merit now: "cumulative % of buffers that have a usage count of >=2". On a system that benefits from having a big buffer cache, that number will be quite large (86% in the first of the samples in the spreadsheet). On one that doesn't, it will be small (35% on the last system listed there). You really have to tune those two types of workload differently. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] optimizer behavior in the case of highly updated tables
Mark Rostron wrote: - It was necessary to take a site outage and perform a “vacuum full analyze” on the table - Following this, the query plan reverted to the more efficient btree lookup Clearly, the garbage buildup resulting from transaction activity on the table is the villain here. - Is it possible to calculate expected space usage given row count and average row size - At what point might the ratio of “expected”/”actual” space usage be able to indicate the need to perform “full vacuum”, or similar maintenance I think you're right to focus on this part, because with your usage pattern--deleting all old data constantly--you have to get this under control in order for the query planner to do the right thing here. Start by reading http://wiki.postgresql.org/wiki/VACUUM_FULL What you probably should have done in order to return to sane query plans was to run CLUSTER instead of VACUUM FULL. Site would have been down less time, and you actually made some future problems a bit worse by screwing your indexes up some using the problematic FULL. As for measuring what's going wrong here, what you want to do is run this around once a week during a slow period and save the output for analysis: VACUUM VERBOSE; This will give you a bunch of statistics about space usage, including a summary at the end that will tell if there's a serious problem you might already be running into (running out of space in the free space map). Combine that with a look at pg_stat_user_tables and you should have a decent initial statistical picture of what's going on with your data. There are two large scale things you may need to consider if volume on your site expands in the future: 1) Deleting the historical data in smaller chunks and doing an immediate VACUUM afterwards. Perhaps as often as daily. This keeps the amount of dead space VACUUM has to clean up as small as possible, at the expensive of higher site overhead. 2) Start partitioning your tables. This allows you to just DROP old partitions rather than deleting rows from a single master table. It can make this whole class of problem go away. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Details about pg_stat_bgwriter
Scott Marlowe wrote: -Average checkpoint frequency -Average size of each checkpoint -Average rate at which new buffers are allocated -Average rate of writes out of the buffer cache -Percentage of writes done by checkpoints, the background writer LRU cleaner, and client backends I think you get all or most of that if you just log checkpoints. If you turned on log_checkpoints, and you went through a whole stack of logs summarizing the data it writes out, you can derive the first two of those from it. I happen to think that sampling two data points and pasting into a spreadsheet is less hassle to deal with, and the data is definitely there by default. You can answer questions like "what's the biggest checkpoint I've seen?" from the stuff in the logs a bit easier than from pg_stat_bgwriter. After for the rest, checkpoints are one of the three possible ways that buffers can be written out. The other two are the background writer's cleaning and client backends. log_checkpoints gives you the size of one of those sources of writes. You can also compute an estimated proportion between the three types of writes from a single snapshot of pg_stat_bgwriter data. But that doesn't help once you've reached the point where you want to change something and measure how response changes afterwards. That requires more regularly sampling the data, so you have a delta between two times. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Details about pg_stat_bgwriter
Thomas Kellerer wrote: I don't think we have a caching issue though, it's just one (regular) bulk import that does some heavy writes which we need to tune. Read performance is absolutely fine, but everything around vacuum and checkpointing seems to slow down the import massively. So would taking a snapshot before and after this import be the right way to start the analysis? Sure. Might want to grab one at those two points, then at a point right before the next big import happens, so that you have what a relatively idle period looks like for comparison sake. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] check_postgres_bloat
dx k9 wrote: check_postgres checks for both index and table bloat. It looks like my indexes are ok, this is just picking up on table bloat. I'm not sure what I can do to reclaim the wasted space other than vacuum full & analyze. Maybe a pgdump will do it. CLUSTER will rebuild a new copy of the table without any table bloat, and it's much faster than VACUUM FULL. See http://wiki.postgresql.org/wiki/VACUUM_FULL Note that the bloat estimate from check_postgres is extremely rough and it's quite possible to get misleading results from it. I wouldn't do anything just based on an initial report from it that a table is bloated other than move the thresholds up until it stops complaining. The idea is that once calibrated usefully to what is normal bloat levels for your app by its measurement technique, you then monitor for excess bloat outside of historical norms. You should not assume the number itself is really accurate, and you should do a manual VACUUM VERBOSE against the table to see if it's right or not before taking drastic action (like VACUUM FULL or CLUSTER). You might also use pg_freespacemap instead to compute more accurate bloat numbers, but most people consider that too much work relative to the improvement you get over the simpler check_postgres estimate. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [ADMIN] redhat and postgresql management
David Bear wrote: I have RHEL 5.4 (64bit) that has a postgresql-lib 8.1 installed to satisfy dependancy for a dozen other packages including apache, php and other apache modules, and subversion. I want to install postresql 8.3 but yum informs me of the version conflict of the library. May i safely just over write the postgresql-lib with the 8.3 lib ?? No. I wrote a detailed guide to exactly how to step around this problem by installing the compat-postgresql-libs package provided by the PostgreSQL 8.3 package set you may find useful: http://www.westnet.com/~gsmith/content/postgresql/pgrpm.htm -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Tuning knobs
Cliff Pratt wrote: What are the main 'knobs' that can be used to tune PostgreSQL? http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postmaster taking up 100% of CPU
higeon wrote: The following is my top log and ps log: Try using "top -c" on Linux instead. Then you'll be able to see what all the processes are doing. Any heavy query will cause a CPU to consume 100% of its time when running, so there may be nothing wrong other than you have queries to optimize better. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
Thomas Kellerer wrote: I'm wondering about the differences when the failover situation occurs. From reading the docs, I get the impression that 9.0's streaming replication might be faster than 8.4's WAL shipping, but otherwise offers the same level of data protection. Is there a difference in how much data could potentially be lost in case of a failover? E.g. because 9.0 replicates the changes quicker than 8.4? There's nothing that 9.0 does that you can' t do with 8.4 and the right software to aggressively ship partial files around. In practice though, streaming shipping is likely to result in less average data loss simply because it will do the right thing to ship new transactions automatically. Getting the same reaction time and resulting low amount of lag out of an earlier version requires a level of external script configuration that few sites every actually manage to obtain. You can think of the 9.0 features as mainly reducing the complexity of installation needed to achieve low latency significantly. I would bet that if you tried to setup 8.4 to achieve the same quality level in terms of quick replication, your result would be more fragile and buggy than just using 9.0--the bugs would be just be in your own code rather than in the core server. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
Thomas Kellerer wrote: The manual lists three possible solutions to HA: shared disk failover, file system replication and Warm/Hot Standby. I'm not an admin (nor a DBA), so my question might sound a bit stupid: from my point of view solutions using shared disk failover of file system replication seem to be more reliable in terms of how much data can get lost (and possibly the switch over lag) Yes, but if you try you'll discover that actually getting any shared disk or file system replication solution setup so that you really do achieve less failover loss than the file shipping approach will be expensive, complicated, fragile in its own way, and just generally a pain to pull off. The fundamental problem with shared storage for example is how to keep a note that's failed from try to reassume being the master when it comes back. Doing that well requires hardware support aimed at that specific use case. Meanwhile, file shipping for Warm Standby use requires nothing special at all except some modest software setup. It's comparatively simple to setup, validate, and keep going on any hardware capable of running the database. This is why shared storage and the like isn't the only obvious solution even though it's technically capable of losing less transactions; you'll discover that keeping from losing that last little bit of data when there's a crash turns out to be quite expensive. Whether it worth it or not depends on the value of your data and whether it can be retained at some higher level when this happens instead. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
Brad Nicholson wrote: One further thing to mention - all of these solutions are based on making the physical blocks available (actually, I'm not sure about Streaming replication in 9.0). You're right here; the SR feature in 9.0 is essentially near real-time partial WAL file shipping, and the WAL contains physical disk block change data. If your master has data blocks corrupted, the next time you do a base backup against it that corruption will be mirrored to the standby too. I've mentioned on one of these lists recently that I like to schedule a periodic pg_dump even if log shipping is the main backup mechanism for a database, just so that corruption in the underlying files is caught as early as possible by trying to read every block and confirm it has valid data. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Stumped by a version conflict.
Patric Michael wrote: /usr/share/pgsql/postgresql.conf.sample /var/lib/pgsql/data/postgresql.conf /home/archives/db/postgresql.conf /home/postgres/data/postgresql.conf The first line is the --datadir as reported by pg_configure --configure The second line is the 7.4 data dir according to its PG_VERSION The third line was a directory backup I made when I upgraded to 8.1 The fourth line is the most recently updated database according to the time stamps. (three days ago) Is there a PG_VERSION suggesting the 4th one is an 8.1 system? It's completely possible you manually started the database cluster like this: pg_ctl start -D /home/postgres/data And that's the one that's been running for the last two years. The default system PostgreSQL install is going to point at /var/lib/pgsql/data by default and try to start whatever database is there, which is the 7.4 one and therefore won't start with your current binaries. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How much RAM is too much ?
Scott Marlowe wrote: On some architectures, as you add memory the memory itself becomes slowerNowadays it's nothing so drastic. Last week I saw an Intel system where the measured memory speed dropped about 30% when going from half filled (36GB) to completely filled (72GB). So it can still be pretty drastic. That only turned into about a 10% drop in actual throughput running the database though, which was more than made up for by reduced reading from the disk due to the extra caching. Stepping back from that trivia, generally adding memory helps only when the working size of the data you access frequently didn't fit in there already. The working set and the total database size are loosely related, but not directly. Other than these weird cases where memory slows down when there's too much of it due to motherboard limitations, there are few situations where adding more RAM hurts performance. However, note that the amount of memory you dedicated to the database--what the shared_buffers tunable controls--has been reported by multiple people I've talked with to top out at somewhere around 8GB of space. If you increase that parameter beyond that size, it could end up hurting performance rather than helping. Since PostgreSQL also uses the operating system cache, this doesn't make for a real limit in the amount of memory the database can use. You just shouldn't give much more than this amount to the database. A second concern to be aware of is that if you're using Linux in particular, it's easy to get the operating system in a state where it has cached way too many writes, which causes upticks in latency when the database issues its periodic checkpoints. I can easily make a Linux system with 72GB of RAM pause regularly for >30 seconds if I try to trigger this behavior. If you write heavily to the database, it's something to be concerned about with that OS and large memory systems. In news I don't consider unrelated, FreeBSD is now working on adding DTrace support: http://freebsdfoundation.blogspot.com/2010/06/dtrace-userland-project.html which will give me yet another reason to consider deploying on that OS instead of Linux. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL on Solaris future
Scott Marlowe wrote: Oracle's answer to my queries to have a new version made? "Why don't you make it?" While I'm sure someone will step in to help out if this gets botched on Oracle's side, I know if I were running any production Solaris installs that counted on packaged builds of PostgreSQL I'd be leaning how to do that myself fast--before they just remove resources related to it without warning anyone (again). -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] The function of lock file named ".s.PGSQL..lock" on Linux?
Tom Lane wrote: We can't portably lock the socket file itself, so we make a separate ordinary file for locking purposes. It looks to me like the fact that the .lock file is created is only documented in src/backend/utils/init/miscinit.c , and even there the reason why (what you wrote above) isn't mentioned. Seems like noting this file is created in the unix_socket_directory entry of the docs, and the rationale for why in the source code, would make a useful improvement. Anybody want to write a little documentation patch? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Is regular vacuuming with autovacuum needed?
Tom Lane wrote: On versions where autovacuum is on by default, I would certainly recommend trying to use only autovacuum. cron-driven vacuum still has some uses but they are corner cases. Corner cases implies something a bit more rare than I'd consider the case here. Consider a server where you know you have a large table that ends up with 5% dead rows each day. This will cause autovacuum to kick in to clean up about every 4 days, at the defaults where autovacuum_vacuum_scale_factor = 0.2. When it does finally get to that table, it's going to have a fairly large amount of work to do. If that happens during peak load time on your server, you may find that a painful shock. In that situation, it's completely reasonable to manually vacuum that table each day during a known slow period, late at night for example. Then it will never get to where it's so bloated that a hefty autovacuum kicks in at an unpredictable time. The other alternative here is to just tune autovacuum so it runs really slowly, so it won't kill responsiveness during any peak period. While in theory that's the right thing to do, this is much harder to get working well than what I just described. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How To: LARGE html text or csv file COPY FROM?
Lou Picciano wrote:p { margin: 0; }
Both errors above make sense to me; COPY is running into either a cr
or a tab character in each case.
Even though they might seem very similar at first, text mode and CSV
mode work very differently here. To quote TFM at
http://www.postgresql.org/docs/current/static/sql-copy.html , which
covers all this if you dig into it enough:
"CSV mode will both recognize and produce CSV files with quoted values
containing embedded carriage returns and line feeds. Thus the files are
not strictly one line per table row like text-mode files."
So as long as the field is quoted, you can have a CR or tab in the middle.
In text mode: "the following characters /must/ be preceded by a
backslash if they appear as part of a column value: backslash itself,
newline, carriage return, and the current delimiter character." Text
mode will therefore require escaping everything on that list.
So if this is close to being importable in CSV mode, you might just need
to wrap the HTML field with a pair of quotes, and escape any quote
characters that are inside of it. Text mode is going to require a whole
escaping run of all the reserved characters that are input.
--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book
Re: [ADMIN] issue with Postgres uninstallation
Scott Marlowe wrote: You do not need the packages to uninstall them. do this: rpm -qa|grep -i postgres and see what packages are listed. Then rpm -e packagename1 packagename2 ... to remove them all. Note that you may have issues removing the compat-postgresql-libs packages, as things other than the main database sofware you can have installed will rely on those. Here's what I do to get rid of all the rest of them in one easy command: rpm -qa | egrep "^postgresql" | xargs rpm -e -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Ingres statdump equivalent for postgresql
bad...@epc-instore.com wrote: I'm looking for a tool that will enable me to dump the data frequency histograms for a postgresql table. #!/bin/bash DB="$1" TABLE="$2" psql -d $DB -x -c "SELECT tablename,attname,null_frac,avg_width,n_distinct,correlation,most_common_vals,most_common_freqs,histogram_bounds FROM pg_stats where tablename='$TABLE'" Run that, pass it database then table name, and you'll get the data for each column in that table. That should get you started; refer to http://www.postgresql.org/docs/current/interactive/catalog-pg-statistic.html and to http://www.postgresql.org/docs/current/interactive/planner-stats-details.html for more details. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] question about HA in PG 9.0
Kasia Tuszynska wrote: The doc is lovely but all of the examples are for linux, has anyone tired a Hot Standby streaming scenario on windows? Any issues specific to windows? I know that linux and windows environments "should" behave the same but actually running in both of those environments exposes many differences. As there is a certain amount of helper scripting required to get Streaming Replication setup, and the usual scripting languages people tend to write them in (bash, Python, Perl) don't ship with Windows, you will have a bit of extra work to do here. I'm not aware of any production-quality setups that have tested the 9.0 implementation under Windows yet, but the similar hard parts have certainly been done before with earlier versions. I have been reading up on this issue most notably on http://wiki.postgresql.org/wiki/Shared_Storage, where the suggestions have been gravitating to solutions on the physical level. Are there any software level solutions? ( I have a feeling that this may be a stupid question, but I want to implement this in the cloud, and not potentially kill an ami instance) The only way you will implement "Shoot the other node in the head" on EC2 approaching the same level of reliability a hardware solution provides is to do just that: don't begin failover to a new node until you've made sure the unreponsive master has been killed, and its AMI instance disabled from powering back up again. Automating that is exactly where I plan to go with redundant EC2 deployments of this type. But you could do it as part of a manual failover if you had to. You cannot let the original master touch the shared data store ever again once it's been taken out of the loop. If your storage is on EBS, I'm not sure how that handles two attempts to attach a writer to the store, but you'd want to test that out too. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book
Re: [ADMIN] files under pg_clog directories are missing
Dinesh Bhandary wrote: Due to hardware crash we ran into issues where some blocks were corrupted and some files were missing. I was able to get over the corrupted blocks ( errmsg - "invalid page header in block 12345 of realtion x") by setting zero_damaged_pages=0 and running vacuum afterwards. Now I am running into situations where pg_clog files are missing (errmsg - "could not open pg_clog/0D0D). I have a backup which is quite old ( considering this as a last resort). Is there any other way to fix this problem? I also created empty blocks to fool postgres, but there are so many of these file missing I was wondering if there a better/faster way to fix this problem. I hope you made a whole backup of the files before you started trying to fix the damage too. It's possible to try and fix this using tricks like zero_damaged_pages and dummy clog files, only to make things worse. We do data recovery services here, and I've had to roll back to the original copy of the data multiple times before in order to try different things before getting a get good copy of someone's data back again. If you don't have a copy of the database yet, do that before you do any more experimenting with the clog files. I wrote a summary of links to past work like this you may find useful, and a little program to create missing pg_clog files that all say "the transaction you're asking about committed", available at: http://archives.postgresql.org/pgsql-general/2009-07/msg00985.php You might further script that to speed up how fast you can fix these as they pop up, which makes the test/correct cycle time go down. You might even write a script that loops over starting the database, looks at the end of the log file, and if it's yet another one of these missing just extract its number, recreate it, and start again. Unfortunately, doing better than that is tricky. We had to modify the PostgreSQL source code to automatically create them in order to handle this safely last time I ran into one of these that was badly corrupted and missing a whole lot of real clog files, not just ones that were unlikely to exist. You should be staring at the numbers of each one of these as they're requested. If the range is way outside of the active clog files you have, that's probably one you can create safely because it's garbage data anyway. But if it starts asking for clog files that are in the middle or near the ends of the set you've got, you may have a bigger problem on your hands. P.S. Make sure you dump a whole copy of the database the minute you get it started again and reload that before you start using it. You have no idea what state all of the tables are really in after a crash like this without such an exercise. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] files under pg_clog directories are missing
Scott Marlowe wrote: As a followup to all this, the OP also needs to figure out why a crashed server could cause so much corruption. This is usually the result of hardware lying about fsync status, i.e. consumer grade hardware pressed into service as a server. See http://www.postgresql.org/docs/9.0/static/wal-reliability.html for more information. Nothing there has changed in 9.0 relative to the version being run here, but the docs for that version got a big upgrade over the earlier ones. Bad memory is always a potential source for issues in this area too. Mostly if you don't know why the crash happened, either. If you know for sure how it crashed, but only then found corruption, Scott's right that it's more likely to be a lying disk drive instead. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] files under pg_clog directories are missing
Dinesh Bhandary wrote: As for the nature of the corruption I still do know know what kind of hardware problems led to this; it happened at one of our clients site and we are still waiting to find out what caused it. One piece of info we got was postgres data directory turned into read only partition. That can happen when a major disk-level problem occurs; system remounts as read-only because it doesn't think it can safely write to it anymore. I'd check into the system kernel logs as soon as possible, to try and find a disk error that kicked the whole thing off. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] question about HA in PG 9.0
Anibal David Acosta wrote: Windows server enterprise has the Microsoft Cluster Services. This enable fail-over scenario between two computers. So, when one of them go offline, the other is activated, this involve many steps, including IP changing, the slave become the master IP address . But you can choose between has a replication between both postgres or maybe you can put your postgresql DATA directory to an external storage shared between those two servers. If this case, when the second server become available the Cluster service map map the DATA unit and starts the postgres service. So, you have everything running and working in aprox. 10 seconds. All of the Windows Server stuff via Microsoft Cluster Services I'm aware of does its shared storage node fencing via sending specific SCSI calls (PERSISTENT RESERVE) to the storage. That probably works fine if you have a Fiber Channel array that supports that. But I don't think there's any flexibility for alternative types of deployments. As for the complexity of the setup time, while they have put a bunch of GUIs on it I've never see anything that tries to do complicated storage array arrangements work in any time that isn't measure in large multiples of hours, so "10 seconds" I'm skeptical of. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book
Re: [ADMIN] xid wraparound
Mark Rostron wrote: # select relname, age(relfrozenxid), relfrozenxid FROM pg_class WHERE relkind = 'r' and relname = 'error'; relname |age| relfrozenxid -+---+-- error | 286569433 | 3781443077 autovacuum_freeze_max_age is the default of 200m, so I assume age(relfrozenid) triggered the autovac processing we are currently seeing? Looks that way. The anti-wraparound work starts long before there is any danger of wraparound; only 10% of the way as you've seen here. The idea is that this gives you plenty of time to work through VACUUM even on a very large table, which is the situation you're in now. Also, maintenance_work_mem is 256MB. And the table size is 132GB. Should we increase maintenance_work_memory? if we increase maintenance_work_memory and reload, will the autovacuum workers pick up the change on the fly? I assume that killing the auto-vacuum workers is not a good idea? If we do, would auto-vac restart, with the increased memory allocation? Here's how you check that sort of thing: postgres=# select name,context from pg_settings where name='maintenance_work_mem'; name | context --+- maintenance_work_mem | user This shows that maintenance_work_mem will pick up a change each time a new user session starts, so no need for a full server restart. So long as you do a regular kill, and not "kill -9", it shouldn't be dangerous to kill the AV workers. You can expect them to turn around and start right back up again though, doing the same job; make any server parameter changes active before killing them. Also, some additional logging you probably want to turn on here: log_autovacuum_min_duration log_checkpoints And take a look at all the data for this table in pg_stat_user_tables , which will show you a variety of vacuum and autovacuum influencing data. If you have the RAM, increasing this parameter should help vacuum out. But your current setting is big enough that it shouldn't be limiting things too badly, and from the slow rate of progress you're seeing, it sounds more like you're hitting some sort of disk bottleneck instead. Either that, or your autovacuum cost parameters are really restricting the activity of the workers to a minimum. If you are just suffering from general system performance limits here, you might follow some of the usual advice at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server to speed things up. You can do a change to checkpoint_segments on the fly too. shared_buffers you'll have to do a full server restart for. Those are the main three (along with maintenance_work_mem) that impact how fast VACUUM work progresses. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] could not connect to server: Connection refused (0x0000274D/10061)
Anuj Pankaj wrote: I installed Postgres 8.1.4 on Linux and postgres server is running and I successfully created database on local machine. I modified listen_addresses = '*' in Postgresql.conf and however If I try to access postgres through pgAdmin III. I am sticking with an exception. Exception is: could not connect to server: Connection refused (0x274D/10061) Is the server running on host "192.168.9.126" and accepting TCP/IP connections on port 5432? My guess is that you have the iptables firewall running on this server, and it doesn't allow remote users to connect to port 5432 on this server. This is usually the case in a default RedHat/CentOS Linux system for example. On that distribution, if you run system-config-securitylevel as root you'll get to a text interface to change settings around. Go into the "Customize" section, and add "postgres:tcp" to the "Other ports" section. Running "service iptables restart" afterwards will make the change active. On other distributions, you can usually confirm whether this is the problem by running (again as root) "iptables -L". If you see any "(policy REJECT)" in there, and there isn't also a reference to the postgres port on the list of what is accepted, you'll need to find the firewall reconfiguration tool. It is possible there's a firewall somewhere else between the server and your system causing the problem instead, but from the fact that you're using the 192.168.*.* block I'd assume your client and the server are on the same local LAN. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book
Re: [ADMIN] how to PG close session
Little, Douglas wrote: The only admin function that look close is pg_terminate_backend(pid int) boolean Terminate a backend I tried it and it couldn't be found. It is newist. We're running PG8.2.13/GP3.3.7 Yes, that's an 8.4 function. We tried pgpool for awhile, but I think I'll have a look at the code first. I did find in the JDBC doc connections.close which should do the trick I suspect. Does your Java app have an application server like Tomcat in it? Those can be setup to work about as well as a PostgreSQL specific pooler, and you get tighter integration with the application itself normally in the process. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] vacuum full table - internals in 8.3
Kieren Scott wrote: I'm trying to understand what is going on internally when doing a VACUUM FULL on a table in 8.3. The info you've gotten from Kevin is all correct, but you may find some of the additional trivia in this area collected at http://wiki.postgresql.org/wiki/VACUUM_FULL interesting as well. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book
Re: [ADMIN] newer release branch
Marc Fromm wrote: There were no links or other helpful information provided. Has anyone upgraded from 8.1 to the latest version 9.0.1? Can you do it in one jump? Can it be done using yum on a RHEL server? As of RHEL 5.5, there is now an official postgresql84 package you can easily load. What I would do in your situation is target upgrading from 8.1 to 8.4 initially, via the dump/reload method, with the main expected problem being the type case changes already mentioned. I've saved a number of good links on this general topic onto http://wiki.postgresql.org/wiki/Version_History that should provide some additional guidance if you read through them a bit. By the time you get that done, 9.0 should be a better tested and stable release, and you can do a quick binary upgrade from 8.4 to 9.0 using the pg_upgrade utility. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Restricting schema sizes
Kieren Scott wrote: What is the best way to restrict/limit the size that a schema can grow too in Postgresql? ... The other option I can think of is writing a script that monitors the size of the objects within a schema. The danger here is that a user could potentially create a huge table as a result of a bad query (cartesian join etc) and fill the application tablespace / filesystem. You have answered your own question here. There isn't any facility in PostgreSQL yet to enforce disk space usage, so if this requirement is a must it's something you'll have to build yourself. The tablespace->filesystem mapping you suggested is probably a good idea to house these things at, to prevent one user from taking out the main part of the database with something they do. One way that you can try to limit the damage of rogue queries on top of that is to set statement_timeout so they just get cancelled if they run for too long. If the tables are being populated by a single statement and you set that to a moderate amount of time, that should be effective at cutting off any of the really bad ones after they've run for a while. You'll have to experiment at just how long that timeout should be. If you set log_min_duration_statement (which is a general good idea in this situation anyway) and look at what kind of runtime common intense but not crippling queries take, that's one way to get feedback on where the timeout should be. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book
Re: [ADMIN] pg_ctl: server does not shut down
Gnanakumar wrote: 1. What could be the root cause of PostgreSQL server not shutting down ever? 2. Am I following a different/incorrect way of disabling PITR? You can check if PITR is disabled by executing: SHOW archive_command; At a psql prompt. If that's empty, then you turned it off, and it isn't involved in the shutdown issue you have anymore. Normally when the server doesn't want to shut down, that's because of a client that won't exit normally, not something in the main server. Take a look at what processes are still running against the database and see if there are clients attached after the fast shutdown attempt. If so, those are your problem, not something to PITR. If you know every client has been stopped or is doing nothing useful, you can do an immediate shutdown of the server and kick everyone off. You'll get messages about unclean shutdown, but no data should be lost. The "service" scripts don't do that though. You'll need to do something like this instead, presuming you start as root: su postgres pg_ctl -D /var/lib/pgsql/data -m immedate stop -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, "PostgreSQL 9.0 High Performance"Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] how do you manage postgres 9's postgres process's memory
Benjamin Krajmalnik wrote: I ordered some massive servers, and in the course of benchmarking them following their instructions it exposed apparent hardware issues -- so the 2 massive servers as well as one of the smaller ones got shipped back to the supplier for them to check it out. Had I assumed the servers were performing as the specs on paper indicated, I would have been in serious trouble a few months down the line as we would have reached limitations we were not aware of. As a general statistic on this topic, about 1 out of every 3 servers I come across has a serious problem of this sort. Maybe half of those are just software misconfiguration that can be fixed before deployment, the rest are more serious issues. Nobody should every trust their hardware vendors to get things right, everyone's production schedules are just too tight nowadays relative to how complicated systems are. Benchmarking yourself is the only reasonable defense against this very common problem. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Re: [ADMIN] Moving the data directory
Sairam Krishnamurthy wrote: I am using Ubuntu 10.04 and Postgres 8.4, I have been trying to move my data dir to another device for the past couple of days but ran into a lot of problems. I did the following: 1. Copy the contents of /var/lib/postgresql/8.4/main to my folder. 2. Changed the data_directory in postgresql.conf. When I start postgres using /etc/init.d script I get the following error, * Starting PostgreSQL 8.4 database server * Error: could not exec /usr/lib/postgresql/8.4/bin/pg_ctl /usr/lib/postgresql/8.4/bin/pg_ctl start -D /media/extension/var/lib/postgresql/8.4/main/ -l /var/log/postgresql/postgresql-8.4-main.log -s -o -c config_file="/etc/postgresql/8.4/main/postgresql.conf" : Debian and Ubuntu installations have a unique feature where you can have multiple PostgreSQL installations installed at the same time. Accordingly, the standard ways of managing the database described in the regular PostgreSQL documentation don't always work. See http://www.stuartellis.eu/articles/postgresql-setup/ for a quick intro to this, specifically the "Using Multiple PostgreSQL Clusters" section. What you may need to change here is not the database's postgresql.conf, but instead the database cluster list you see when running pg_lsclusters. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres on NAS/NFS
Bryan Keller wrote: It sounds like NFS is a viable solution nowadays. I a still going to shoot for using iSCSI, given it is a block-level protocol rather than file-level, it seems to me it would be better suited to database I/O. Please digest carefully where Joe Conway pointed out that it took them major kernel-level work to get NFS working reliably on Linux. On anything but Solaris, I consider NFS a major risk still; nothing has improved "nowadays" relative to when people used to report regular database corruption running it on other operating systems. Make sure you read http://www.time-travellers.org/shane/papers/NFS_considered_harmful.html and mull over the warnings in there before you assume it will work, too. I don't think I've ever heard from someone happy with an iSCSI deployment, either. The only way you could make an NFS+iSCSI storage solution worse is to also use RAID5 on the NAS. I'd suggest taking a look at http://wiki.postgresql.org/wiki/Shared_Storage and consider how you're going to handle fencing issues as well here. One of the reasons SANs tend to be preferred in this area is because fencing at the fiber-channel switch level is pretty straightforward. DAS running over fiber-channel can offer the same basic features though, it's just not as common to use a switch in that environment. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Corrupted disk
Tony Nelson wrote: I am ok, because I have a dump from last night, and wal since then. Dumps made with pg_dump are easy to restore from. But they're fixed in time: there is no applying WALs to them in order to update them. Anything that's happened to the server since then you can't add. If you made a filesystem copy of the server using pg_start_backup/pg_stop_backup, and you save all of the WAL files after the backup began, that's also useful. You can store from that backup and apply all the WAL that's happened since then. Can I drop/create/restore from this dump? Or should I restore from last nights full and apply the WAL? If last night's backup with a filesystem one done with pg_start_backup/pg_stop_backup, and you have WAL since then, I would favor that set as likely to work fine. But it sounds like what you have might instead be a pg_dump backup and some WAL files; you can't apply the WAL to such a dump. Whatever you do, you want to make a full filesystem copy of the server's data directory--with the server shutdown--before you do anything else. It's possible to recover from page errors and extract the available data using the right data recovery techniques, especially if there's a pg_dump available too; we offer some services in this area. But if any serious changes are made to the database before we get to it, odds of successful recovery can drop fast. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Checkpoint and Background Writer Statistics
Sam Nelson wrote: One of our clients has an lru_max_dirty setting of 1000 and a bgwriter_delay of 200, but we're still seeing much higher growth in the buffers_checkpoint than buffers_clean, with buffers_checkpoint increasing at about 20 times the rate that buffers_clean is. We don't see very much growth in the maxwritten_clean, though - something like one every couple of days. The background writer can only clean data pages that haven't been used in a while. In your case, it sounds like most of the things that are getting dirty are being touched enough that they never meet its criteria. There's nothing wrong with that. Writing out dirty buffers only once per checkpoint is in theory the most efficient way to handle regularly changed data. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Re: best practice for moving millions of rows to child table when setting up partitioning?
On 04/27/2011 03:35 PM, Mark Stosberg wrote: In particular, I wanted to check whether the UPDATE statement would alter all the rows automatically, or if the underlying trigger would cause all the rows processed a row at a time. It appears from my test that the result of the UPDATE was going to appear all at once. I'm worried about the resource implications of inserting mullions of rows all at once. You can put a LIMIT on an UPDATE, same as any other type of query. No reason that style of migration must happen all at once, you can just target a smaller number of rows at a time and slowly siphon rows over to the children by iteration. I don't see anything inherently wrong with the approach you're proposing. CREATE INDEX CONCURRENTLY does something similar to the logic you've outlined--mark where data was inserted at, move over all older data, then just copy over the new rows at the end. The main downsides of that is complexity and the need for downtime to get an atomic swap to using the child tables. You can't add them to the parent until the original is gone, if you've inserted duplicate data into them. I'm not sure what all that complexity buys you, compared to just adding all the children, putting a limit on the UPDATE, and looping over that with some delay after each iteration until it's finished if you want to further control the rate. There's usually no reason you have to be in a rush to moving data over. Relying on the database's transactional scheme to avoid making any mistakes here--making it so a goof will ROLLBACK--and avoiding any need for downtime are normally higher priorities in a partition migration than making the move happen as fast as possible. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] archive_timeout behavior (8.4.6)
On 04/22/2011 08:23 PM, Brian Fehrle wrote: So long story short, if a cluster has zero activity, and archive_timeout is not set to 0, when it reaches that timeout should it generate a file, or just skip because it would be 100% empty? You're right about what you've seen here, there is some logic in the WAL/checkpoint code that can avoid doing anything when the timeout hits, if there hasn't been any new WAL written since the last one. That hasn't changed for a while now though (since I've been watching that part of the source code at least) so I'm not sure what differences between 8.4.6 and earlier 8.4 versions you think you've observed. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] WAL and Checkpoints documentation
jtke...@verizon.net wrote: Does anyone know of any good presentations/whitepapers that spell out WAL and check-pointing functionality other than the ADMIN guide http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm is the main free reference that tries to step outside of the documentation into real-world tuning advice. There are a few of my talks at http://projects.2ndquadrant.it/talks that mention material around this area too; "Inside the PostgreSQL Buffer Cache" and "The Write Stuff" both overlap with checkpoint issues quite a bit. Beyond that, my book is the main extended commentary discussing this area available. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Strange behavior of child table.
On 06/01/2011 02:07 AM, Jenish wrote: select * from Round_Action where action_id =5 limit 100 → execution time 80 sec select * from Round_Action_CH1 action_id =5 limit 100 → execution time 0.1 sec First off: each of the child tables needs to have the index created on them. That doesn't get inherited just by putting it on the master. If you already did that, try running these both with "EXPLAIN". Sharing the two query plans here would help figure out what's happening. Showing the definition of the index on one of the child tables would be helpful too. There are some optimizer limitations in PostgreSQL versions before 9.0 that prevent it from using an index on the child tables in some situations where people expect it to, which includes aggregates like MIN/MIN. I'm not sure if your LIMIT case is running into the same issue, the plan will help confirm what's going on. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] patching the OS of a 9.0.4 db with hot standby
Ray Stell wrote: I want to do OS patching on prod and hot standby. What is the advised procedure for maintenance on the two systems? I would think patching the standby should come first in case it blows something up. Oracle has a defer switch you can throw on prod to tell it to relax on log shipping to the standby. Do we have that here? Your intuition here is reasonable--test on the standby, if things go wrong consider reversing the patch, with no impact on the master. It's not covered in the main because that tends not to cross over into OS management considerations. The main parameter you could consider tweaking in this case is wal_keep_segments, which will let you adjust how long the master queues things for the standby before it gives up. If you really do lose the standby, you can run out of disk space on the master eventually if you haven't set this properly. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Streaming replication status and fail over questions
Michael Holt wrote: 1) I've seen things about using pg_current_xlog_location(), pg_last_xlog_replay_location(), pg_last_xlog_receive_location() to check replication status, but how can this tell me either the time lag or actual query lag? Do I need to wait for 9.1 and it's replication monitoring features? You might want to check out repmgr: http://projects.2ndquadrant.com/repmgr It can collect data in the background that it uses to compute lag in time units. 2) If I have a master to multi-slave setup and need to fail over, is there anyway for slaves to detect the new master? Without this it seems like fail over could be pretty messy. repmgr also provides a view to help make this easier to figure out right now, and the next version due out any day now will go even further toward automating it completely. 3) Finally just wanted to confirm that SR allows only for replication of an entire server. Well, an entire database cluster on a server. I have put more than one database cluster on a server before in order to make it possible to replicate only a subset of the data. But that's difficult to pull off, you end up needing tools like dblink for anything that crosses the two databases together. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Prepared statments: partial indexes are avoided!
On 06/20/2011 10:55 AM, adrien ducos wrote: I think this should at least be writen in this documentation: http://www.postgresql.org/docs/9.0/static/indexes-partial.html as a known limitation to avoid people spending hours of search for why is the application becoming slow. It's documented in the "Notes" section of http://www.postgresql.org/docs/9.0/static/sql-prepare.html because it's not specific to partial indexes. Prepared statements are very risky when you run them against queries with a very skewed distribution, like your case where 99% of the values for a column are the same. They help to prevent SQL injection and can reduce parsing/planning overhead, but prepared statements can have terrible performance in some unfortunately common situations. Just recently I had one clients end up switching to another ORM specifically because they couldn't get the one they started with to stop using prepared statements, and those gave them terrible query plans. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How to pg_upgrade an 8.4.3 Installation
On 06/20/2011 09:23 AM, Nils Gösche wrote: Can I simply use the 9.0.4 pg_upgrade on this since the bug has been fixed in 9.0.4, or would I first have to upgrade the 8.4 installation to 8.4.8? Unless this 8.4 install was itself upgraded from 8.3 using pg_upgrade, you can go right from 8.4.3 to 9.0.4. The reason for the fix in 8.4.8 was to take care of people who use(d) pg_upgrade to upgrade from 8.3 to 8.4. Those people needed 8.4.8 released with the bug fix in order to correct the problem that could be in their 8.4 databases. If you did an 8.3->8.4 pg_upgrade on this system before, you might want to do the standard fix suggested using 8.4.8. But if this has always been an 8.4 system, you can just go right to 9.0.4. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Problem with Streaming Replication
On 06/30/2011 08:21 AM, abraao895 wrote: - The HD of the PC1(master) dead. The WAL file don't have replicated because it is a asynchronous proccess and suppose that this already didn't have happened. - The PC2(slave) doesn't have the last record. That's exactly how some transaction loss can happen in this situation. Some software worried about this problem maintains a small transaction log outside of the database, so that it's possible to reconstruct really critical information after such a disaster. In PostgreSQL 9.1, due to be released later this year, synchronous replication is available on a per-transaction basis. That resolves the concern you have--important transactions can be confirmed on one of the slaves as a requirement before they commit. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD Comprehensive and Customized PostgreSQL Training Classes: http://www.2ndquadrant.us/postgresql-training/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication_timeout does not seem to be working
On 07/22/2011 12:11 PM, A J wrote: Basically I wish my synchronous write transaction to not wait indefinitely when the synchronous standby servers are not available. But rather a response returned back to client that write could not be successful, after trying for 'n' seconds. How can that be accomplished ? You might be able to get what you want here by setting statement_timeout to 'n' seconds when doing a write transaction. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
Re: [ADMIN] snapshot backup with WAL files
On 07/29/2011 11:35 AM, Isabella Ghiurea wrote:
I would like to configure our Pg db for OS file system snapshot
backup with Pg WAL files, need to know if would be enough in pg db
to call :
|*SELECT* *pg_start_backup*('pitr_backup');
and next take the snapshot and after
pg_stop the backup and close the WAL files .
This procedure works fine. But note that doing this does not eliminate
the need to save log files using the archive_command. You will end up
with a small number of them, which is an advantage compared to
non-snapshot backup techniques where hundreds of archive logs might
occur during the backup time. But even with using a snapshot backup,
the small number that are referenced in the backup log are still
necessary for the backup to be useful.
As always, your database backup should not be considered useful until
you've done a successful restore using it. That would have discovered
this problem during testing.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication from Oracle to PostgreSQL?
On 08/11/2011 03:58 PM, Joshua D. Drake wrote: I would imagine it would be pretty simple to fork the triggers that do replication in Londiste or Slony (as long as everything else was PostgreSQL) to be able to replicate from Oracle to PostgreSQL. That's the approach taken by some of our in-house tools. "simple" wouldn't be the right word to describe the process that went into building them though. You have to get the basics working, then worry about non-deterministic statements (random, timestamps, etc.), sequencing, all of the stuff any statement based replication can be faced with--except now there's the quirks of two databases to worry about. One of the reasons Slony and Londiste statement extraction works as well as it does is because new functions were added to PostgreSQL 8.3 for them, to make it easier to get the sort of snapshot information needed to grab consistent copies of data. That problem comes back again when the origin database isn't a PostgreSQL one too. There are a couple of ways to solve this problem, but all of them are complicated enough that they're only sitting in commercial/consultant projects right now (in addition to ones mentioned here already, I'm sure Continuent has some applicable stuff too). I think Kevin's customer may be in for a surprise at how much it will cost to duplicate this feature set from scratch--I'd be surprised to see that total come in under what licensing an already built package would sell for. Companies who aren't willing to throw some money toward buying/customizing at least some PostgreSQL software, if it allows escaping from Oracle licensing, should reconsider their strategy. Not everything you'll want to make a conversion easy is going to be free. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] streaming replication and wraparound vacuuming
On 09/30/2011 12:28 PM, Andrew Shved wrote: I have a set up where most of the data located in partitions of the large table and that data never changes. The only table that really changes is current day's partition. The problem is that transaction wraparound vacuum generates a lot of traffic quite frequently on partitions that never changed (technically read only). This is especially a problem with streaming replication due to enormous amounts of logging activity that is being generated. Is there a way to limit or eliminate those frequent vacuum. Something akin to oralce read only tablespace or a way to set frozen transaction xid on a partition level? Basically anything to make it less frequent especially when there is zero activity on those tables after initial loads. There's not really a good answer to this yet. Write-once data is common enough that I'd like to see some accelerated paths for adding it appear. I'm not aware of anyone working in that direction right now though. Normally writes in PostgreSQL can result in blocks being written out many times: -pg_xlog image -Initial write to data block on disk -Write(s) to update hint bits -Initial VACUUM -XID wraparound VACUUM FREEZE If you aggressively run VACUUM FREEZE yourself, setting vacuum_freeze_min_age and possibly autovacuum_freeze_max_age to lower values than the defaults, you might avoid some number of these multiple writes, which are wasted from your perspective. Aim to commit and freeze as fast as possible afterwards, avoiding some of the intermediate writes you might otherwise see. That's the best that can be done without some hacking on the server code. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
Re: [ADMIN] background writer being lazy?
On 10/31/2011 07:28 PM, Brian Fehrle wrote: The main thing I am currently seeing is that there are 300X or more buffers written by checkpoints rather than background writer. Writing buffers at checkpoint time is more efficient than having the background writer handle them. I think your efforts to space checkpoints out may have backfired a bit on you. You're letting 40 minutes of dirty buffers accumulate before they're written out. Putting checkpoint_timeout closer to its default of 5 minutes again may reduce the spikes you're seeing. The changes you've made to the background writer configuration are also counterproductive, given that it's not really going to trigger anyway. I would only recommend decreasing bgwriter_delay or increasing bgwriter_lru_maxpages or you see the total_clean_max_written value get incremented regularly. If that's not happening, making the background writer run more often and try to do more work just adds overhead. Also: you've set shared_buffers to 16GB. That's beyond where most people find increases to stop being useful. I'd wager you'll get less spiky performance just by lowering that a lot. The 256MB to 1GB range is where I normally end up on servers where lower latency is prioritized instead of maximum throughput. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
Re: [ADMIN] cancelled queries on standby
On 11/15/2011 05:46 AM, MirrorX wrote: i am facing an issue concerning cancelled queries made on a hot standby server. if we suppose that there are some tables that are never changed (no inserts/updates/deletes) and that autovacuum is set to false for these tables, what else could there be affecting these tables and consequently cancelling my queries? thx in advance for any suggestions Query cancellation isn't targeted this way. Whether or not a query needs to be canceled by vacuum cleanup depends on whether any open transaction can see the rows that are being cleaned up. I think you have the understandable belief what rows are actually being viewed by the query running on the standby matter; they don't. Let's say you have a long-running query Q accessing a table A on the standby. Then some vacuum cleanup of table B happens on the master. Q can then be canceled when those cleanup records are replicated. The fact that A is never changed and doesn't have autovacuum enabled doesn't matter. Because it's possible for Q to see B, it's canceled regardless of whether it's looked at it yet. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Giving postgres roles 'sudo'-like access
On 12/19/2011 01:04 PM, Mario Splivalo wrote: I need to have postgres role to be able to cancel queries run by that same role. This feature is close to commit as a new one for PostgreSQL 9.2: http://archives.postgresql.org/message-id/4eeb3a2b.8090...@2ndquadrant.com http://archives.postgresql.org/pgsql-hackers/2011-12/msg00822.php You might use some of that code and make your own pg_cancel_backend-like function that loads into the database. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Monitor without superuser?
On 02/17/2012 10:37 AM, Jan Nielsen wrote: I would like to create a role "dba_monitor" for a portion of my PG 9.1 user-base to be able to monitor PG but without granting superuser. You want to create a security definer function for this: http://www.postgresql.org/docs/9.1/static/sql-createfunction.html -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] about shared_buffer and kernel.shmmax
On 06/20/2012 11:38 PM, wangqi wrote: > shared_buffer maximum limit is ? > I set shared_buffer more than 250G, I got error at startup pg. There's not much evidence that values larger than 16GB are really productive. You really do not want put all their memory in just that one place. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replication recovery/startup question
On 06/25/2012 11:40 AM, Rob Cowell wrote: Why would the output from ‘ls’ show older filenames (013D...xx) as newer in date than the “013F...xx” filenames? Does Postgres re-cycle old log filenames ? It recycles old log files. If you turn on log_checkpoints, you can see how many and how often. It will list a count of recycled WAL files at each checkpoint, along with how many of the old ones were just deleted instead. The weird pattern in the timestamps you're seeing is a state in the middle of doing that, and yes they look quite weird sometimes. The files are noteed as reusable, get re-initialized to hold new data (they're not overwritten completely with zeros like new WAL files are), and renamed to a new segment number. And each of those steps has a corresponding flush to disk step which makes sure the filesystem metadata is updated. Some of the middle states there are unusual. Does the output from ‘ps’ mean the master/slave are in sync, or is the slave really still playing catchup (based on the names of the logfiles in pg_xlog) ? Your example was in sync, with the file names just being odd due to the implementation of WAL file recycling. You might also check pg_stat_replication to get an easier view of things, rather than relying on ps. ps is correct, it's just harder to check. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
