[ADMIN] Why auto vacuum almost running all the time on one toast table?
v9.1.3 on Ubuntu 10.04 server. I have one table which has frequent insert and weekly deletion, no update. Recently, I found the auto vacuum on the toast table of it almost running all the time. each run took around 1 hour. I don't think there are so many inerts so that it reaches the auto vacuum criteria(20%? changes). And there is no such frequent auto vacuum on the main table. The last auto vacuum of the main table is on 3rd Jun. what might cause this behavior? # grep pg_toast_16922 postgresql-2012-06-12_00.log 2012-06-12 01:22:56 CST [@] CONTEXT: automatic vacuum of table oodb.pg_toast.pg_toast_16922 2012-06-12 04:27:49 CST [@] CONTEXT: automatic vacuum of table oodb.pg_toast.pg_toast_16922 2012-06-12 06:58:02 CST [@] CONTEXT: automatic vacuum of table oodb.pg_toast.pg_toast_16922 2012-06-12 09:27:38 CST [@] CONTEXT: automatic vacuum of table oodb.pg_toast.pg_toast_16922 2012-06-12 12:17:33 CST [@] CONTEXT: automatic vacuum of table oodb.pg_toast.pg_toast_16922 2012-06-12 15:05:31 CST [@] CONTEXT: automatic vacuum of table oodb.pg_toast.pg_toast_16922 -- 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 setup PostgreSQL using Windows Authentication?
On 06/12/2012 10:00 AM, Peter Cheung wrote: Hi, I'm new to PostgreSQL. I installed PostgreSQL on a Windows Server 2008 R2 server. I have created a database and an user in Windows Active Directory. How can I configure that user to access that database? By default, Windows users and PostgreSQL users are completely separate. Use PgAdmin-III to create the user, or a CREATE USER command in psql. See http://www.postgresql.org/docs/9.1/static/user-manag.html It is also possible to use SSPI authentication with PostgreSQL, so PostgreSQL authenticates users against Active Directory. I haven't used it myself. The user must still be created in PostgreSQL, SSPI just takes care of authenticating them using their Windows credentials. See: http://www.postgresql.org/docs/9.1/static/auth-methods.html -- Craig Ringer
[ADMIN] Merging two databases
Hi there, Is there a way of merging two databases in postgresql in the way of differential replication? The thing is that we have two DBs with same schemas and we want to merge the data within them into one single replica. Are there any tools/ways of doing this? Of course when the merged db is created it will be only updated/inserted with new data from each of source DBs. -- Łukasz Brodziak -- 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 rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: No such file or directory
On Thu, Jun 7, 2012 at 8:01 PM, Fabricio fabrix...@hotmail.com wrote: Hi. I have this problem: I have PostgreSQL 9.1.3 and the last night crash it. This was the first error after an autovacuum (the night before last): 2012-06-06 00:59:07 MDT 814 4fceffbb.32e LOG: autovacuum: found orphan temp table (null).tmpmuestadistica in database dbRX 2012-06-06 01:05:26 MDT 1854 4fc7d1eb.73e LOG: could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: No such file or directory 2012-06-06 01:05:28 MDT 1383 4fcf0136.567 ERROR: tuple concurrently updated 2012-06-06 01:05:28 MDT 1383 4fcf0136.567 CONTEXT: automatic vacuum of table global.pg_catalog.pg_attrdef 2012-06-06 01:06:09 MDT 1851 4fc7d1eb.73b ERROR: xlog flush request 4/E29EE490 is not satisfied --- flushed only to 3/13527A10 2012-06-06 01:06:09 MDT 1851 4fc7d1eb.73b CONTEXT: writing block 0 of relation base/311360/12244_vm 2012-06-06 01:06:10 MDT 1851 4fc7d1eb.73b ERROR: xlog flush request 4/E29EE490 is not satisfied --- flushed only to 3/13527A10 2012-06-06 01:06:10 MDT 1851 4fc7d1eb.73b CONTEXT: writing block 0 of relation base/311360/12244_vm 2012-06-06 01:06:10 MDT 1851 4fc7d1eb.73b WARNING: could not write block 0 of base/311360/12244_vm 2012-06-06 01:06:10 MDT 1851 4fc7d1eb.73b DETAIL: Multiple failures --- write error might be permanent. Last night it was terminated by signal 6. 2012-06-07 01:36:44 MDT 2509 4fd05a0c.9cd LOG: startup process (PID 2525) was terminated by signal 6: Aborted 2012-06-07 01:36:44 MDT 2509 4fd05a0c.9cd LOG: aborting startup due to startup process failure 2012-06-07 01:37:37 MDT 2680 4fd05a41.a78 LOG: database system shutdown was interrupted; last known up at 2012-06-07 01:29:40 MDT 2012-06-07 01:37:37 MDT 2680 4fd05a41.a78 LOG: could not open file pg_xlog/000100030013 (log file 3, segment 19): No such file or directory 2012-06-07 01:37:37 MDT 2680 4fd05a41.a78 LOG: invalid primary checkpoint record And the only option was pg_resetxlog. After this a lot of querys showed me this error: 2012-06-07 09:24:22 MDT 1306 4fd0c7a6.51a ERROR: missing chunk number 0 for toast value 393330 in pg_toast_2619 2012-06-07 09:24:31 MDT 1306 4fd0c7a6.51a ERROR: missing chunk number 0 for toast value 393332 in pg_toast_2619 I lost some databases. I restarted the cluster again with initdb and then I restored the databases that I could backup (for the other I restored an old backup) no space or permissions problem. No filesystem or disk error. Can you help me to know what happened? I'd say that everything still points to a filesystem error. Have you tried unmounting it and running an offline check? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Why auto vacuum almost running all the time on one toast table?
Rural Hunter ruralhun...@gmail.com writes: v9.1.3 on Ubuntu 10.04 server. I have one table which has frequent insert and weekly deletion, no update. Recently, I found the auto vacuum on the toast table of it almost running all the time. each run took around 1 hour. I don't think there are so many inerts so that it reaches the auto vacuum criteria(20%? changes). And there is no such frequent auto vacuum on the main table. The last auto vacuum of the main table is on 3rd Jun. what might cause this behavior? Hm, is the autovacuum managing to complete, or is it getting kicked off before it can complete? It would help to see the messages those CONTEXT lines are attached to; and/or you might check pg_stat_all_tables to see when the last completed autovacuum was for both the main table and its toast table. regards, tom lane -- 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] pg_basebackup blocking all queries with horrible performance
On Tue, Jun 12, 2012 at 2:37 AM, Lonni J Friedman netll...@gmail.com wrote: On Fri, Jun 8, 2012 at 7:29 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Jun 9, 2012 at 4:30 AM, Lonni J Friedman netll...@gmail.com wrote: On Thu, Jun 7, 2012 at 11:04 PM, Craig Ringer ring...@ringerc.id.au wrote: On 06/08/2012 09:01 AM, Lonni J Friedman wrote: On Thu, Jun 7, 2012 at 5:07 PM, Jerry Sieversgsiever...@comcast.net wrote: You might try stopping pg_basebackup in place with SIGSTOP and check if problem goes away. SIGCONT and you should start having sluggishness again. If verified, then any sort of throttling mechanism should work. I'm certain that the problem is triggered only when pg_basebackup is running. Its very predictable, and goes away as soon as pg_basebackup finishes running. What do you mean by a throttling mechanism? Sure, it only happens when pg_basebackup is running. But if you *pause* pg_basebackup, so it's still running but not currently doing work, does the problem go away? Does it come back when you unpause pg_basebackup? That's what Jerry was telling you to try. If the problem goes away when you pause pg_basebackup and comes back when you unpause it, it's probably a system load problem. If it doesn't go away, it's more likely to be a locking issue or something _other_ than simple load. SIGSTOP (kill -STOP) pauses a process, and SIGCONT (kill -CONT) resumes it, so on Linux you can use these to try and find out. When you SIGSTOP pg_basebackup then the postgres backend associated with it should block shortly afterwards as its buffers fill up and it can't send more data, so the load should come off the server. A throttling mechanism refers to anything that limits the rate or speed of a thing. In this case, what you want to do if your problem is system overload is to limit the speed at which pg_basebackup does its work so other things can still get work done. In other words you want to throttle it. Typical throttling mechanisms include the ionice and renice commands to change I/O and CPU priority, respectively. Note that you may need to change the priority of the *backend* that pg_basebackup is using, not necessarily the pg_basebackup command its self. I haven't done enough with Pg's replication to know how that works, so someone else will have to fill that bit in. Thanks for your reply. I've confirmed that issuing a SIGSTOP does eliminate the thrashing, and issuing a SIGCONT resumes the thrash. I've looked at iostat output both before during pg_basebackup runs, and I'm not seeing any indication that the problem is due to disk IO bottlenecks. The numbers don't vary very much at all between the good bad times. This is typical when pg_basebackup is running: Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util md0 0.00 0.00 67.76 68.62 4.42 1.46 88.34 0.00 0.00 0.00 0.00 0.00 0.00 and this is when the system is ok: Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util md0 0.00 0.00 68.04 68.56 4.44 1.46 88.39 0.00 0.00 0.00 0.00 0.00 0.00 I looked at vmstat output, but nothing is jumping out at me as being dramatically different when pg_basebackup is running. swap in and swap out are zero 100% of the time for the good bad perf cases. I can post example output if someone is interested, or if there's something specific that I should be looking at as a potential problem, let me know. Did you set synchronous_standby_names to '*'? If so, the problem you encountered can happen. When synchronous_standby_names is '*', you cannot control which standbys take a role of synchronous standby. The standby which you expect to run as asynchronous one might be synchronous one. So my guess is that at first one of your three standbys was running as synchronous standby, and all queries were executed normally. But when you started pg_basebackup, pg_basebackup unexpectedly got the role of synchronous standby from another standby. Since pg_basebackup doesn't send the information about replication progress back to the master, all queries (more precisely, transaction commit) got stuck, and kept waiting for the reply from synchronous standby. You can avoid this problem by setting synchronous_standby_names to the names of your standbys instead of '*'. I don't have synchronous_standby_names set at all. I'm only doing asynchronous replication. Hmm... I have no idea about what happened on your environment, for now. Could you show me the self-contained test case? Regards, -- Fujii Masao -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Hot backup for postgres 8.4
Hi there, I have been looking for a good solution to backup a postgresql 8.4 database server (not pg_dump) and the only options that it seems that I have are either a Omnipitr or a custom-coded solution. I am a little bit afraid about setting up omipitr in production for archiving backup, even though it has been running seamlessly on a testing environment. I wish I could use pg_basebackup but unfortunately it is V. 9 oriented. Does anyone have production experience with Omnipitr ? If not, is there something out there trustworthy enough for production use ? Thanks in advance, A.A. -- 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] pg_basebackup blocking all queries with horrible performance
On Tue, Jun 12, 2012 at 10:49 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 12, 2012 at 2:37 AM, Lonni J Friedman netll...@gmail.com wrote: On Fri, Jun 8, 2012 at 7:29 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Jun 9, 2012 at 4:30 AM, Lonni J Friedman netll...@gmail.com wrote: On Thu, Jun 7, 2012 at 11:04 PM, Craig Ringer ring...@ringerc.id.au wrote: On 06/08/2012 09:01 AM, Lonni J Friedman wrote: On Thu, Jun 7, 2012 at 5:07 PM, Jerry Sieversgsiever...@comcast.net wrote: You might try stopping pg_basebackup in place with SIGSTOP and check if problem goes away. SIGCONT and you should start having sluggishness again. If verified, then any sort of throttling mechanism should work. I'm certain that the problem is triggered only when pg_basebackup is running. Its very predictable, and goes away as soon as pg_basebackup finishes running. What do you mean by a throttling mechanism? Sure, it only happens when pg_basebackup is running. But if you *pause* pg_basebackup, so it's still running but not currently doing work, does the problem go away? Does it come back when you unpause pg_basebackup? That's what Jerry was telling you to try. If the problem goes away when you pause pg_basebackup and comes back when you unpause it, it's probably a system load problem. If it doesn't go away, it's more likely to be a locking issue or something _other_ than simple load. SIGSTOP (kill -STOP) pauses a process, and SIGCONT (kill -CONT) resumes it, so on Linux you can use these to try and find out. When you SIGSTOP pg_basebackup then the postgres backend associated with it should block shortly afterwards as its buffers fill up and it can't send more data, so the load should come off the server. A throttling mechanism refers to anything that limits the rate or speed of a thing. In this case, what you want to do if your problem is system overload is to limit the speed at which pg_basebackup does its work so other things can still get work done. In other words you want to throttle it. Typical throttling mechanisms include the ionice and renice commands to change I/O and CPU priority, respectively. Note that you may need to change the priority of the *backend* that pg_basebackup is using, not necessarily the pg_basebackup command its self. I haven't done enough with Pg's replication to know how that works, so someone else will have to fill that bit in. Thanks for your reply. I've confirmed that issuing a SIGSTOP does eliminate the thrashing, and issuing a SIGCONT resumes the thrash. I've looked at iostat output both before during pg_basebackup runs, and I'm not seeing any indication that the problem is due to disk IO bottlenecks. The numbers don't vary very much at all between the good bad times. This is typical when pg_basebackup is running: Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util md0 0.00 0.00 67.76 68.62 4.42 1.46 88.34 0.00 0.00 0.00 0.00 0.00 0.00 and this is when the system is ok: Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util md0 0.00 0.00 68.04 68.56 4.44 1.46 88.39 0.00 0.00 0.00 0.00 0.00 0.00 I looked at vmstat output, but nothing is jumping out at me as being dramatically different when pg_basebackup is running. swap in and swap out are zero 100% of the time for the good bad perf cases. I can post example output if someone is interested, or if there's something specific that I should be looking at as a potential problem, let me know. Did you set synchronous_standby_names to '*'? If so, the problem you encountered can happen. When synchronous_standby_names is '*', you cannot control which standbys take a role of synchronous standby. The standby which you expect to run as asynchronous one might be synchronous one. So my guess is that at first one of your three standbys was running as synchronous standby, and all queries were executed normally. But when you started pg_basebackup, pg_basebackup unexpectedly got the role of synchronous standby from another standby. Since pg_basebackup doesn't send the information about replication progress back to the master, all queries (more precisely, transaction commit) got stuck, and kept waiting for the reply from synchronous standby. You can avoid this problem by setting synchronous_standby_names to the names of your standbys instead of '*'. I don't have synchronous_standby_names set at all. I'm only doing asynchronous replication. Hmm... I have no idea about what happened on your environment, for now. Could you show me the self-contained test case? I'm running the following, which gets piped over ssh to a remote server (at gigabit ethernet
Re: [ADMIN] How to setup PostgreSQL using Windows Authentication?
According to http://www.postgresql.org/docs/9.1/static/auth-methods.html, SSPI only works when GSSAPI is available and GSSAPI support has to be enabled when PostgreSQL is built. Does it mean that I need to uninstall PostgreSQL and reinstall it with GSSAPI support? I used the One click installer downloaded from http://www.postgresql.org/download/windows to install PostgreSQL and I'm not sure how to include GSSAPI support. From: Craig Ringer [mailto:ring...@ringerc.id.au] Sent: Tuesday, June 12, 2012 1:11 AM To: Peter Cheung Cc: 'pgsql-admin@postgresql.org' Subject: Re: [ADMIN] How to setup PostgreSQL using Windows Authentication? On 06/12/2012 10:00 AM, Peter Cheung wrote: Hi, I'm new to PostgreSQL. I installed PostgreSQL on a Windows Server 2008 R2 server. I have created a database and an user in Windows Active Directory. How can I configure that user to access that database? By default, Windows users and PostgreSQL users are completely separate. Use PgAdmin-III to create the user, or a CREATE USER command in psql. See http://www.postgresql.org/docs/9.1/static/user-manag.html It is also possible to use SSPI authentication with PostgreSQL, so PostgreSQL authenticates users against Active Directory. I haven't used it myself. The user must still be created in PostgreSQL, SSPI just takes care of authenticating them using their Windows credentials. See: http://www.postgresql.org/docs/9.1/static/auth-methods.html -- Craig Ringer
Re: [ADMIN] pg_basebackup blocking all queries with horrible performance
On Tue, Jun 12, 2012 at 8:37 PM, Lonni J Friedman netll...@gmail.com wrote: On Tue, Jun 12, 2012 at 10:49 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 12, 2012 at 2:37 AM, Lonni J Friedman netll...@gmail.com wrote: On Fri, Jun 8, 2012 at 7:29 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Jun 9, 2012 at 4:30 AM, Lonni J Friedman netll...@gmail.com wrote: On Thu, Jun 7, 2012 at 11:04 PM, Craig Ringer ring...@ringerc.id.au wrote: On 06/08/2012 09:01 AM, Lonni J Friedman wrote: On Thu, Jun 7, 2012 at 5:07 PM, Jerry Sieversgsiever...@comcast.net wrote: You might try stopping pg_basebackup in place with SIGSTOP and check if problem goes away. SIGCONT and you should start having sluggishness again. If verified, then any sort of throttling mechanism should work. I'm certain that the problem is triggered only when pg_basebackup is running. Its very predictable, and goes away as soon as pg_basebackup finishes running. What do you mean by a throttling mechanism? Sure, it only happens when pg_basebackup is running. But if you *pause* pg_basebackup, so it's still running but not currently doing work, does the problem go away? Does it come back when you unpause pg_basebackup? That's what Jerry was telling you to try. If the problem goes away when you pause pg_basebackup and comes back when you unpause it, it's probably a system load problem. If it doesn't go away, it's more likely to be a locking issue or something _other_ than simple load. SIGSTOP (kill -STOP) pauses a process, and SIGCONT (kill -CONT) resumes it, so on Linux you can use these to try and find out. When you SIGSTOP pg_basebackup then the postgres backend associated with it should block shortly afterwards as its buffers fill up and it can't send more data, so the load should come off the server. A throttling mechanism refers to anything that limits the rate or speed of a thing. In this case, what you want to do if your problem is system overload is to limit the speed at which pg_basebackup does its work so other things can still get work done. In other words you want to throttle it. Typical throttling mechanisms include the ionice and renice commands to change I/O and CPU priority, respectively. Note that you may need to change the priority of the *backend* that pg_basebackup is using, not necessarily the pg_basebackup command its self. I haven't done enough with Pg's replication to know how that works, so someone else will have to fill that bit in. Thanks for your reply. I've confirmed that issuing a SIGSTOP does eliminate the thrashing, and issuing a SIGCONT resumes the thrash. I've looked at iostat output both before during pg_basebackup runs, and I'm not seeing any indication that the problem is due to disk IO bottlenecks. The numbers don't vary very much at all between the good bad times. This is typical when pg_basebackup is running: Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util md0 0.00 0.00 67.76 68.62 4.42 1.46 88.34 0.00 0.00 0.00 0.00 0.00 0.00 and this is when the system is ok: Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util md0 0.00 0.00 68.04 68.56 4.44 1.46 88.39 0.00 0.00 0.00 0.00 0.00 0.00 I looked at vmstat output, but nothing is jumping out at me as being dramatically different when pg_basebackup is running. swap in and swap out are zero 100% of the time for the good bad perf cases. I can post example output if someone is interested, or if there's something specific that I should be looking at as a potential problem, let me know. Did you set synchronous_standby_names to '*'? If so, the problem you encountered can happen. When synchronous_standby_names is '*', you cannot control which standbys take a role of synchronous standby. The standby which you expect to run as asynchronous one might be synchronous one. So my guess is that at first one of your three standbys was running as synchronous standby, and all queries were executed normally. But when you started pg_basebackup, pg_basebackup unexpectedly got the role of synchronous standby from another standby. Since pg_basebackup doesn't send the information about replication progress back to the master, all queries (more precisely, transaction commit) got stuck, and kept waiting for the reply from synchronous standby. You can avoid this problem by setting synchronous_standby_names to the names of your standbys instead of '*'. I don't have synchronous_standby_names set at all. I'm only doing asynchronous replication. Hmm... I have no idea about what happened on your environment, for now. Could you show me the self-contained test case? I'm running the
[ADMIN] How to install Postgresql with GSSAPI support using One click installer?
Hi, I have installed Postgresql using One click installer on a Windows Server. How can I check whether GSSAPI support is enabled so I can use SSPI for Windows Authentication? Thanks.
Re: [ADMIN] Merging two databases
On 06/12/2012 06:23 PM, Lukasz Brodziak wrote: Hi there, Is there a way of merging two databases in postgresql in the way of differential replication? The thing is that we have two DBs with same schemas and we want to merge the data within them into one single replica. Are there any tools/ways of doing this? I think you'll want Bucardo or Slony-I for this. You certainly can't do it with the built-in replication. -- Craig Ringer -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
