Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
Queue - Event are stored in queue tables i.e queues. Several producers can write into same queue and several consumers can read from the queue. Events are kept in queue until all the consumers have seen them. Maybe you have some inactive consumers holding a event tables. qadmin -h -p 5432 -U postgres -d -Q Use 'show help;' to see available commands. copy output of show consumer command show consumer; Rene From: pgsql-general-ow...@postgresql.org <pgsql-general-ow...@postgresql.org> on behalf of Leonardo M. Ramé <l.r...@griensu.com> Sent: Thursday, May 19, 2016 2:43 PM To: PostgreSql-general Subject: Re: [GENERAL] Londiste 3 pgq events_1_1 table huge El 18/05/16 a las 19:03, Rene . escribió: > Hi, Check for long running Idle in transaction sessions. Idle in transaction > sessions may holding events table from cleaning itself up. > If there is more then days long running idle in transaction sessions, kill > them, event table should be cleaned automatically after that. > > "select pid,state, query_start from pg_stat_activity where state='idle in > transaction';" for checking sessions. > > Rene Thanks Rene, I found only one "idle in transaction" and it dates from just a couple of minutes ago, so, the reason of huge event table must be something else. By looking at the event_1_1 table I found records from march, but londiste3 status shows everything is already in sync: nodo_master (root) | Tables: 146/0/0 | Lag: 0s, Tick: 1112197 +--: node_esclavo (leaf) Tables: 146/0/0 Lag: 0s, Tick: 1112197 So, what if I manually delete old events?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
Hi, Check for long running Idle in transaction sessions. Idle in transaction sessions may holding events table from cleaning itself up. If there is more then days long running idle in transaction sessions, kill them, event table should be cleaned automatically after that. "select pid,state, query_start from pg_stat_activity where state='idle in transaction';" for checking sessions. Rene From: pgsql-general-ow...@postgresql.org <pgsql-general-ow...@postgresql.org> on behalf of Leonardo M. Ramé <l.r...@griensu.com> Sent: Wednesday, May 18, 2016 10:23 PM To: PostgreSql-general Subject: [GENERAL] Londiste 3 pgq events_1_1 table huge Hi, I couldn't find a mailing list or forum to ask londiste related questions, so I hope someone from this list can help me with this. I have a Londiste based replication setup that is working perfectly since last year. Now I noted the events_1_1 table grew too much (almost exactly the same size of the whole database), so I'm asking how can I clean up the events table without breaking the replication?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Role inheritance and Conflicting Parameter Values
To impersonate another role, as a superuser or group member use: SET SESSION AUTHORIZATION user1; http://www.postgresql.org/docs/9.3/static/sql-set-session-authorization.html Regards. 2014-06-23 8:25 GMT-05:00 McGehee, Robert robert.mcge...@geodecapital.com: Hi, I'm trying to set the default statement_timeout parameter for two different groups of users. However, as many of the users inherit from overlapping roles, I wanted to get clarification on how inheritance of parameter values worked when a role inherits from other roles that all have different settings for a parameter value. For instance, assume three roles: 'user1', 'role2', 'role3'. The 'user1' role can login and inherits from 'role2' and 'role3'. ALTER ROLE user1 SET statement_timeout=0; ALTER ROLE role2 SET statement_timeout=1000; ALTER ROLE role3 SET statement_timeout=3000; In this scenario, which value of statement_timeout does user1 get? Also, if the primary role (user1) doesn't have a value set, how is the parameter inherited between role2 and role3? Also, as a superuser, can I examine the parameter values for another user to make sure I set this correctly. For instance, I'd like to run a query in the spirit of this invalid query: SHOW statement_timeout FOR user1;. Unfortunately, SHOW only shows my parameters, though it would be nice to examine other accounts. Thanks, Robert *Robert McGehee, CFA* Geode Capital Management, LLC One Post Office Square, 20th Floor | Boston, MA | 02109 Direct: (617)392-8396 *This e-mail, and any attachments hereto, are intended for use by the addressee(s) only and may contain information that is (i) confidential information of Geode Capital Management, LLC and/or its affiliates, and/or (ii) proprietary information of Geode Capital Management, LLC and/or its affiliates. If you are not the intended recipient of this e-mail, or if you have otherwise received this e-mail in error, please immediately notify me by telephone (you may call collect), or by e-mail, and please permanently delete the original, any print outs and any copies of the foregoing. Any dissemination, distribution or copying of this e-mail is strictly prohibited. * -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: [GENERAL] Role inheritance and Conflicting Parameter Values
As far as I'm concerned, you cannot inherit role settings, only privileges. http://www.postgresql.org/docs/9.3/static/role-membership.html 2014-06-23 20:12 GMT-05:00 Rene Romero Benavides rene.romer...@gmail.com: To impersonate another role, as a superuser or group member use: SET SESSION AUTHORIZATION user1; http://www.postgresql.org/docs/9.3/static/sql-set-session-authorization.html Regards. 2014-06-23 8:25 GMT-05:00 McGehee, Robert robert.mcge...@geodecapital.com : Hi, I'm trying to set the default statement_timeout parameter for two different groups of users. However, as many of the users inherit from overlapping roles, I wanted to get clarification on how inheritance of parameter values worked when a role inherits from other roles that all have different settings for a parameter value. For instance, assume three roles: 'user1', 'role2', 'role3'. The 'user1' role can login and inherits from 'role2' and 'role3'. ALTER ROLE user1 SET statement_timeout=0; ALTER ROLE role2 SET statement_timeout=1000; ALTER ROLE role3 SET statement_timeout=3000; In this scenario, which value of statement_timeout does user1 get? Also, if the primary role (user1) doesn't have a value set, how is the parameter inherited between role2 and role3? Also, as a superuser, can I examine the parameter values for another user to make sure I set this correctly. For instance, I'd like to run a query in the spirit of this invalid query: SHOW statement_timeout FOR user1;. Unfortunately, SHOW only shows my parameters, though it would be nice to examine other accounts. Thanks, Robert *Robert McGehee, CFA* Geode Capital Management, LLC One Post Office Square, 20th Floor | Boston, MA | 02109 Direct: (617)392-8396 *This e-mail, and any attachments hereto, are intended for use by the addressee(s) only and may contain information that is (i) confidential information of Geode Capital Management, LLC and/or its affiliates, and/or (ii) proprietary information of Geode Capital Management, LLC and/or its affiliates. If you are not the intended recipient of this e-mail, or if you have otherwise received this e-mail in error, please immediately notify me by telephone (you may call collect), or by e-mail, and please permanently delete the original, any print outs and any copies of the foregoing. Any dissemination, distribution or copying of this e-mail is strictly prohibited. * -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/ -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: [GENERAL] Taking rsynced base-backup without wal-archiving enabled
It depends on the database transactional activity, observe how many new wal files are generated during a period equivalent to what it takes to do your base backup. I would set it to twice that number. Take into account that a checkpoint is issued at the beginning of the process. If you're lazy just try setting it to something very high such as 256 or more to prevent wal files being recycled during the process. 2014-06-23 2:12 GMT-05:00 Jürgen Fuchsberger juergen.fuchsber...@gmx.at: Hi all, Can I do a consistent file-system-level backup using the following procedure: 1) SELECT pg_start_backup(...) 2) rsync postgres data dir to another server 3) SELECT pg_stop_backup() 4) rsync pg_xlog directory From what I understand this should be similar to running pg_basebackup using the -x parameter, correct? One caveat seems to be that wal_keep_segments should be set high enough. Can anybody tell what high enough usually is? Thanks in advance for you help! Best regards, Juergen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: [GENERAL] Server continuously enters to recovery mode.
Try dumping the database, you might have a damaged table as in this case: http://www.postgresql.org/message-id/cagwygjwpiffet_k6qvxjjqo_jrqiueu+bszr1hurrh1obv8...@mail.gmail.com good luck. 2014-05-03 18:59 GMT-05:00 David G Johnston david.g.johns...@gmail.com: DrakoRod wrote Hi everybody! I have a problem (really huge problem), I have one server of production, but yesterday in the night I saw this error: * ERROR: could not access status of transaction 2410303155 DETAIL: Could not open file pg_clog/08FA: No such file or directory * Solution: * dd if=/dev/zero/ of=data/pg_clog/08FA bs=256K count=1 * So I ran this solutions, no problem so far, but then (after 1 or 2 hours approximately), the server crash, I think somebody did something but did not tell me, no one did nothing! cool!! ¬¬. I started the server and this began in recovery mode, he started. But after some time (without apparent pattern), the server came into the recovery mode again. After that, the server continuously entering recovery mode, like I said without apparent pattern, between 3, 5 or 10 minutes run normally but then enter in the recovery mode again. I restart the server (began in recovery mode again), started, but after sometime he enter in recovery mode again. Try to recover the server with the PITR and nothing. The server version is 9.0.x in a Linux SUSE. The database size is the 336 GB. Please give me any help to recover the server! Thanks! The only help I'm good for on this topic is to tell you to supply log file data; especially those entries at and just before the server enters recovery mode. Good Luck! David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Server-continuously-enters-to-recovery-mode-tp5802321p5802322.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
[GENERAL] restore_command ignored in recovery.conf on standby
- Base backup taken with 9.2.6 (via pg_basebackup command) - binaries updated to 9.2.8 - set up the base backup to replicate from the master and archives, and started - the restore_command option is ignored, with the following message: 2014-04-13 21:07:21.386 CDT,,,22055,,534b42d7.5627,4,,2014-04-13 21:07:19 CDT,1/0,0,LOG,0,consistent recovery state reached at 1E6/F9FFE880CheckRecoveryConsistency, xlog.c:7371, 2014-04-13 21:07:21.387 CDT,,,22053,,534b42d6.5625,1,,2014-04-13 21:07:18 CDT,,0,LOG,0,database system is ready to accept read only connectionssigusr1_handler, postmaster.c:4261, # recovery.conf standby_mode=on restore_command='/bin/tar -xzf /db/wal_archives/%f.tar.gz -C %p' where the /db/wal_archives/ looks like this: 000101ED00F7.tar.gz 000101ED00F8.tar.gz 000101ED00F9.tar.gz as you can see, the time line is far ahead from where the standby claims to have reached a consistent recovery state I tested the restore_command replacing variables and it works. Any ideas on why it isn't being executed? -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: [GENERAL] restore_command ignored in recovery.conf on standby
Yep, I checked: [postgres@uxmal standby_node]$ /bin/tar -xzf /db/wal_archives/000101ED00FB.tar.gz -C /db/standby_node/pg_xlog/ [postgres@uxmal standby_node]$ echo $? 0 [postgres@uxmal standby_node]$ ls /db/standby_node/pg_xlog/ | grep 000101ED00FB 000101ED00FB I read somewhere that in order for the extracted file to be placed at a custom location you had to use that option -C I'll try rewriting the command and debug it. Thanks for your comment. 2014-04-13 21:39 GMT-05:00 Stephen Frost sfr...@snowman.net: Rene, * Rene Romero Benavides (rene.romer...@gmail.com) wrote: restore_command='/bin/tar -xzf /db/wal_archives/%f.tar.gz -C %p' [...] I tested the restore_command replacing variables and it works. Any ideas on why it isn't being executed? Are you sure that it isn't being executed and just immediately returning '1' (meaning 'false'- aka, done with recovery)? The -C option to tar is supposed to be change directory according to the tar that I've got, and %p is the complete file name that PG wants the WAL file to be copied to- it's not a directory (it's something like pg_xlog/RECOVERY_WAL). Thanks, Stephen -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: [GENERAL] restore_command ignored in recovery.conf on standby
What I did (I bet there's a better way) is this: restore_command='/db/standby_node/scripts/wal_restore.sh %f %p' # wal_restore.sh #!/bin/bash /bin/tar -xzf /db/wal_archives/$1.tar.gz -C /tmp cp /tmp/$1 $2 rm /tmp/$1 My best regards to Stephen Frost. 2014-04-13 21:58 GMT-05:00 Stephen Frost sfr...@snowman.net: * Rene Romero Benavides (rene.romer...@gmail.com) wrote: Yep, I checked: [postgres@uxmal standby_node]$ /bin/tar -xzf /db/wal_archives/000101ED00FB.tar.gz -C /db/standby_node/pg_xlog/ [postgres@uxmal standby_node]$ echo $? 0 Err, sure, but that isn't actually what is being passed via %p. %p will be something like 'pg_xlog/RECOVERY_WAL', as I said, which *won't* work for your tar command, eg: sfrost@tamriel:/home/sfrost tar -xzf zz.tar.gz -C zz/zz tar: zz/zz: Cannot open: No such file or directory tar: Error is not recoverable: exiting now sfrost@tamriel:/home/sfrost echo $? 2 [postgres@uxmal standby_node]$ ls /db/standby_node/pg_xlog/ | grep 000101ED00FB 000101ED00FB N, PG tells you via %p the *specific* filename to use, do not just overwrite files in pg_xlog willy-nilly with a tar command. Thanks, Stephen -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
Re: [GENERAL] Where is diskchecker.pl ?
On Wednesday 18 July 2012 16:11:21 jg wrote: Hi, The PostgreSQK documentation refers to diskchecker.pl on the page http://brad.livejournal.com/2116715.html But on this page, the given link for diskchecker.pl does not exist anymore. After some unsuccessfull queries on Google to find the missing file, I wonder if one of you have a lin or a copy of this file. Thank you. -- Cordialement, Jean-Gérard Pailloncy -- Gosh, can't find it either =-( You can find me on twitter @iCodeiExist -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to make select for multiunit
On 01/14/2012 09:22 PM, plasmasoftware net wrote: hello ... i have stock table all stock in smallest unit ( PCS ) i have stock table like this name qty unit PCA 20 PCS MOUSE 25 PCS i have table unit 1 KRT = 12 PAK 1 PAK = 10 PCS how to make select to display report stok in dbGRID like this : PCA 2 KRT 0 PAK 0 PCS MOUSE 0 KRT 2 PAK 1 PCS I don't understand, Wouldn't have been: PCA 0 KRT 2 PAK 20 PCS MOUSE 0 KRT 2 PAK 25 PCS ? anyways, if the units table was something like this : unit convUnit ratio PCS PCS 1 PCS KRT 1/120 PCS PAK 1/10 (foreseeing other types of conversions) One simple solution is: SELECT s.name, (select s.qty * ratio FROM unit WHERE unit='PCS' and conv='KRT'), ' KRT ', (select s.qty * ratio FROM unit WHERE unit='PCS' and conv='PAK'), ' PAK ', s.qty, ' PCS' FROM stock s; rounding or truncating is up to you. -- http://sharingtechknowledge.blogspot.com/
Re: [GENERAL] [NOVICE] Specific database vars, again...
Glus Xof wrote: Hi again, Maybe, I didn't explain my question enough. I need to record properties that belongs to an specific database (and so, they work at database level... not at global scope: * Could I use the \set statements ? but... the vars defined are not in a database scope but a global, aren't they ?... furthermore, could save these vars when try to dump the database ??? ) * Or, must to create an specific one-row table ?. The first things to ask is: Do multiple transactions change this data? What is something goes wrong - do you need rollback to work? Anyway, a table with one or only a few rows is very likely to be cached in RAM by postgres anyway if you access it regulary. So performance shouldn't be an issue. LG Rene -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance impact of hundreds of partitions
Leonardo F wrote: Is it mainly a planning problem or an execution time problem? I have here a database with a table partitioned across 400 sub-tables. I'm using a trigger-based solution with constraint exclusion. The thing that takes the longest is planning queries. I made THAT problem just go away for the most part by using cached queries (only works within the same database connection, but thats no problem for me). I also tried a rule-based partitioning, that indeed breaks down quickly performance wise. Also, the trigger is a ON INSERT AND UPDATE, and only on the main table. If your main insert/update process knows into which partition to insert, you can speed that up even more; while still beeing able to use the automated partitioning for everything else. LG Rene -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Virtual table with pl/perl?
Alvaro Herrera wrote: Rene Schickbauer escribió: Hi! I'm looking into implementing an updateable virtual table using pl/perl. What i basically want to do is read out/update some external resources. For programs using the SQL interface, this should look just like a regular table except that it isn't ACID compliant (no rollbacks). Did you see DBI-Link in pgfoundry.org? Looks interesting. Thanks. Though i'm not yet sure i wanna go this way, i'd still have to implement the database drivers for the stuff i need. Specifically, i need two things: I need to access external devices via snmp and web, as well as the ActiveDirectory for our windows domain controllers. LG Rene -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Virtual table with pl/perl?
Hi! I'm looking into implementing an updateable virtual table using pl/perl. What i basically want to do is read out/update some external resources. For programs using the SQL interface, this should look just like a regular table except that it isn't ACID compliant (no rollbacks). I'm pretty sure i can make a table, write a bunch of functions and some do-instead rules. But is there a more elegant/simple solution? LG Rene -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
