Re: [GENERAL] Logging at schema level
Schema = tenant. So basically tenant level logging. On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" wrote: > On 21 July 2017 07:10:42 GMT+02:00, Nikhil wrote: > >Hello, > > > >I am using postgresql schema feature for multi-tenancy. can we get > >postgresql logs at schema level. Currently it is for the whole database > >server (pg_log) > > > > What do you want to achieve? Logging of data-changes per tenant? > > Regards, Andreas. > > > -- > 2ndQuadrant - The PostgreSQL Support Company >
[GENERAL] Logging at schema level
Hello, I am using postgresql schema feature for multi-tenancy. can we get postgresql logs at schema level. Currently it is for the whole database server (pg_log) Best Regards, Nikhil
[GENERAL] BDR - Ignore already exists error during DDL replay
Hello, In my 2-node BDR setup if I make changes in db schema I am seeing below error or after few reboots I get into below inconsistent state during DDL replay. Is there any way to ignore ItemAlreadyExists error during DDL replay ? global lock of DDL replication is switched off in configuration. Best Regards, Nikhil GMT%ERROR: trigger "truncate_trigger" for relation "provisionsettings_server_boundary" already e xists <283032016-07-12 15:51:58 GMT%CONTEXT: during DDL replay of ddl statement: CREATE TRIGGER truncate_trigger AFTER TRUNCATE ON public.provisionsettings_server_boundary FOR EACH STATEMENT EXECUTE PROCEDURE bdr.queue_truncate() <3662016-07-12 15:51:58 GMT%LOG: worker process: bdr (6306146678097036401,2,16386,)->bdr (6306138636064436461,1, (PID 2830 3) exited with exit code 1
Re: [GENERAL] 2 node bdr setup gives error in replication slots
I think its caused by hard reboots (may b hyper visor itself is rebooted!) . Is there any setting which can reduce such problems ? On Tue, Jun 7, 2016 at 5:30 PM, Craig Ringer wrote: > On 7 June 2016 at 18:24, Nikhil wrote: > >> I am getting below error in my 2 node BDR setup. postgres going down. any >> idea? >> >> <35382016-06-07 10:16:59 GMT%LOG: database system was interrupted; last >> known up at 2016-06-07 09:06:44 GMT >> <35382016-06-07 10:16:59 GMT%PANIC: replication slot file >> "pg_replslot/bdr_16389_6293051490331141125_2_16389__/state" has >> wrong magic 4522536 instead of 17112225 >> <35352016-06-07 10:16:59 GMT%LOG: startup process (PID 3538) was >> terminated by signal 6: Abort trap >> <35352016-06-07 10:16:59 GMT%LOG: aborting startup due to startup >> process failure >> > > That suggests that there was a write failure on the replication slot file. > > A simple write error shouldn't be possible because we write the slot file > to a tempfile, then replace the old slot file with the new one. Filesystem > issues are possible, or memory corruption in the application that caused a > bad write. Or a bug, but it's hard to see how we could write the wrong slot > magic number here. > > With the slot corrupted all you can really do is part one of the nodes > then join a new one. > > If you're able to reproduce this I'd really like to see how it came about. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
[GENERAL] 2 node bdr setup gives error in replication slots
I am getting below error in my 2 node BDR setup. postgres going down. any idea? <35382016-06-07 10:16:59 GMT%LOG: database system was interrupted; last known up at 2016-06-07 09:06:44 GMT <35382016-06-07 10:16:59 GMT%PANIC: replication slot file "pg_replslot/bdr_16389_6293051490331141125_2_16389__/state" has wrong magic 4522536 instead of 17112225 <35352016-06-07 10:16:59 GMT%LOG: startup process (PID 3538) was terminated by signal 6: Abort trap <35352016-06-07 10:16:59 GMT%LOG: aborting startup due to startup process failure Best Regards, Nikhil
Re: [GENERAL] BDR to ignore table exists error
Thanks a lot Martin for your replies. On Sun, May 29, 2016 at 11:50 PM, Martín Marqués wrote: > Hi, > > El 29/05/16 a las 06:01, Nikhil escribió: > > > > *Nik>> skip_ddl_locking is set to True in my configuration. As this > > was preventing single* > > > > *node from doing DDL operation (if one is down majority is not there > > for doing DDL on available node)* > > Well, you have to be prepared to deal with burn wounds if you play with > fire. ;) > > If you decide to have skip_ddl_locking on you have to be sure all DDLs > happen on one node, else you end up with conflicts like this. > > I suggest you find out why the table was already created on the > downstream node (as a forensics task so you can avoid bumping into the > same issue). > > > Nik>> DDL used is > > > > > > ERROR: relation "af_npx_l3_16_146_10" already exists > > <596802016-05-29 08:53:07 GMT%CONTEXT: during DDL replay of ddl > > statement: CREATE TABLE public.af_npx_license_l3_16_146_ > > 10 (CONSTRAINT af_npx_license_l3_16_146_10_rpt_sample_time_check CHECK > > (((rpt_sample_time OPERATOR(pg_catalog.>=) 146417040 > > 0) AND (rpt_sample_time OPERATOR(pg_catalog.<=) 1464173999))) ) INHERITS > > (public.af_npx_l3) WITH (oids=OFF) > > <554132016-05-29 08:53:07 GMT%LOG: worker process: bdr > > (6288512113617339435,2,16384,)->bdr (6288505144157102317,1, (PID 59 > > 680) exited with exit code 1 > > On the node where the CREATE TABLE is trying to get applied run this: > > BEGIN; > SET LOCAL bdr.skip_ddl_replication TO 'on'; > SET LOCAL bdr.skip_ddl_locking TO 'on'; > DROP TABLE af_npx_l3_16_146_10; > END; > > After that, the DDL that's stuck will get applied and the stream of > changes will continue. > > By the looks of what you're dealing with, I wouldn't be surprised if the > replication gets stuck again on another DDL conflict. > > I suggest rethinking the locking strategy, because this shows that > there's something fishy there. > > Regards, > > -- > Martín Marquéshttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [GENERAL] BDR to ignore table exists error
Please see my replies inline. On Sat, May 28, 2016 at 8:08 PM, Martín Marqués wrote: > El 28/05/16 a las 08:57, Nikhil escribió: > > Once the node which was down is brought back the replication slot is not > > turned active. The reason being replication slot is trying to create a > > partition table which already exists. Because of this error replication > > slot is stuck in inactive mode. Is there any way to ignore this error? > > BTW, how did you end up in such a state? Did you create the partition > table skipping ddl locking? > *Nik>> skip_ddl_locking is set to True in my configuration. As this was > preventing single* > *node from doing DDL operation (if one is down majority is not there for doing DDL on available node)* > > At this point the easiest way out is to drop the table on the node where > it's trying to get applied with bdr_replication off or > skip_ddl_replication on, so the table is dropped locally but not > replicated, and the create table from the slot can be consumed. > > The other option is to consume the create table statement from the slot > directly. > Nik>> DDL used is > ERROR: relation "af_npx_l3_16_146_10" already exists <596802016-05-29 08:53:07 GMT%CONTEXT: during DDL replay of ddl statement: CREATE TABLE public.af_npx_license_l3_16_146_ 10 (CONSTRAINT af_npx_license_l3_16_146_10_rpt_sample_time_check CHECK (((rpt_sample_time OPERATOR(pg_catalog.>=) 146417040 0) AND (rpt_sample_time OPERATOR(pg_catalog.<=) 1464173999))) ) INHERITS (public.af_npx_l3) WITH (oids=OFF) <554132016-05-29 08:53:07 GMT%LOG: worker process: bdr (6288512113617339435,2,16384,)->bdr (6288505144157102317,1, (PID 59 680) exited with exit code 1 > > > Be aware of the dangers of changing the default values for such > parameters, (bdr_replication, skip_ddl_replication, skip_ddl_locking) > and when needed they should be used with special care. > *Nik>>. The DDL replay is started once the node join back to bdr group. I > think its started from an old check point causing partition already exists > error. Is there any way to ignore replay error ? or ignore DDL errors while > replay ?* > > Regards, > > -- > Martín Marquéshttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [GENERAL] BDR to ignore table exists error
Once the node which was down is brought back the replication slot is not turned active. The reason being replication slot is trying to create a partition table which already exists. Because of this error replication slot is stuck in inactive mode. Is there any way to ignore this error? On 28-May-2016 4:56 PM, "Martín Marqués" wrote: > El 27/05/16 a las 06:33, Nikhil escribió: > > Hello, > > > > > > I have a BDR setup with two nodes. If I bring one node down i am seeing > that > > the replication slot is becoming inactive with below error. > > If you take down one of the nodes of a BDR mesh, the replication slots > from each of the upstream nodes it connects to will switch to inactive. > That's how replication slots work. > > > <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%DETAIL: > > streaming transactions committing after 0/111A91 > > 48, reading WAL from 0/110F03F8 > > <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%LOG: > > logical decoding found consistent point at 0/110F03 > > F8 > > <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%DETAIL: > > Logical decoding will begin using saved snapshot > > . > > <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%LOG: > > unexpected EOF on standby connection > > Downstream node got disconnected, which is sensible given that you took > that node down. > > > <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: > > 0.437 ms > > <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: > > 0.462 ms > > <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: > > 0.096 ms > > <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: > > 0.101 ms > > <3462016-05-25 23:58:20 GMT%LOG: starting background worker process "bdr > > (6288505144157102317,1,16384,)->bdr (628851211361 > > 7339435,2," > > It seems you brought up postgres on the downstream node again and it > connected to the replication slot. > > > <798462016-05-25 23:58:20 GMT%ERROR: relation > "af_npx_device_l3_16_149_10" > > already exists > > I'm not sure what happened here. Does that relation exist? > > Run \d+ af_npx_device_l3_16_149_10 with psql on both nodes. > > Also, did replication resume? Check with the lag query from the BDR > documentation. > > Regards, > > -- > Martín Marquéshttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
[GENERAL] BDR to ignore table exists error
Hello, I have a BDR setup with two nodes. If I bring one node down i am seeing that the replication slot is becoming inactive with below error. <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%DETAIL: streaming transactions committing after 0/111A91 48, reading WAL from 0/110F03F8 <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%LOG: logical decoding found consistent point at 0/110F03 F8 <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%DETAIL: Logical decoding will begin using saved snapshot . <10.106.43.152(43253)nsxpostgres798452016-05-25 23:58:19 GMTnsxdb%LOG: unexpected EOF on standby connection <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: 0.437 ms <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: 0.462 ms <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: 0.096 ms <127.0.0.1(31185)nsxroot792492016-05-25 23:58:19 GMTnsxdb%LOG: duration: 0.101 ms <3462016-05-25 23:58:20 GMT%LOG: starting background worker process "bdr (6288505144157102317,1,16384,)->bdr (628851211361 7339435,2," <798462016-05-25 23:58:20 GMT%ERROR: relation "af_npx_device_l3_16_149_10" already exists Thanks & Regards, Nikhil
Re: [GENERAL] BDR replication slots
On interface down: -- <10.102.31.213(27599)postgres13082016-04-19 06:31:36 GMTprocess_journal%LOG: terminating walsender process due to replication timeout Once interface is brought back 425906 <12692016-04-19 08:32:58 GMT%LOG: starting background worker process "bdr (6275149074578269365,2,16386,)->bdr (6275135922714263763,1," 425907 <597732016-04-19 08:32:58 GMT%ERROR: relation "mygroup" already exists 425908 <597732016-04-19 08:32:58 GMT%CONTEXT: during DDL replay of ddl statement: CREATE TABLE public.mygroup (id pg_catalog."varchar"(14) NOT NULL COLLATE pg_catalog."default", name pg_catalog."varchar"(100) COLLATE pg_catalog."default", device_type pg_catalog."varchar"(30) COLLATE pg_catalog."default", platform_type pg_catalog."varchar"(30) COLLATE pg_catalog."default", CONSTRAINT mygroup_pkey PRIMARY KEY (id) ) WITH (oids=OFF) 425909 <12692016-04-19 08:32:58 GMT%LOG: worker process: bdr (6275149074578269365,2,16386,)->bdr (6275135922714263763,1,(PID 59773) exited with exit code 1 425910 <10.102.31.213(13467)postgres597742016-04-19 08:32:59 GMTprocess_journal%LOG: starting logical decoding for slot "bdr_16386_6275135922714263763_1_16386__" 425911 <10.102.31.213(13467)postgres597742016-04-19 08:32:59 GMTprocess_journal%DETAIL: streaming transactions committing after 0/1014CEE8, reading WAL from 0/1014A920 425912 <10.102.31.213(13467)postgres597742016-04-19 08:32:59 GMTprocess_journal%LOG: logical decoding found consistent point at 0/1014A920 425913 <10.102.31.213(13467)postgres597742016-04-19 08:32:59 GMTprocess_journal%DETAIL: There are no running transactions. 425914 *<10.102.31.213(13467)postgres597742016-04-19 08:32:59 GMTprocess_journal%LOG: unexpected EOF on standby con**nection* On Tue, Apr 19, 2016 at 10:29 AM, Alvaro Aguayo Garcia-Rada < aagu...@opensysperu.com> wrote: > Hello, > > What do you see on each node's log after enablibg interfaces? > > Regards, > > Alvaro Aguayo > Jefe de Operaciones > Open Comb Systems E.I.R.L. > > Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: > (+51) 954183248 > Website: www.ocs.pe > > Sent from my Sony Xperia™ smartphone > > > Nikhil wrote > > > Hello, > > I have a 2 node BDR group and replication is happening properly. if i > bring down one of the node's interface, after sometime the replication > slots are becoming inactive (pg_replication_slots view). Then if i bring > back interface slots are not turning active automatically and replication > stops. Any idea why automatically its not re-established ? > > Best Regards, > Nikhil >
[GENERAL] BDR replication slots
Hello, I have a 2 node BDR group and replication is happening properly. if i bring down one of the node's interface, after sometime the replication slots are becoming inactive (pg_replication_slots view). Then if i bring back interface slots are not turning active automatically and replication stops. Any idea why automatically its not re-established ? Best Regards, Nikhil
Re: [GENERAL] Custom conflict handlers
Can someone help with bdr conflict handlers? What is ch_proc ..? On 13-Feb-2016 8:11 pm, "Nikhil" wrote: > This is for system that use BDR (Bi-Directional Replication). BDR > documentation > http://bdr-project.org/docs/next/functions-conflict-handlers.html talks > about postgresql functions. I am looking for samples. > > Best Regards, > Nikhil > > On Sat, Feb 13, 2016 at 5:31 PM, Nikhil wrote: > >> Hello, >> >> Is there any documentation with details of how to custom conflict >> handlers? Wanted to write update-update conflict handler. Any help is >> highly appreciated. >> >> Best Regards, >> Nikhil >> > >
Re: [GENERAL] Custom conflict handlers
This is for system that use BDR (Bi-Directional Replication). BDR documentation http://bdr-project.org/docs/next/functions-conflict-handlers.html talks about postgresql functions. I am looking for samples. Best Regards, Nikhil On Sat, Feb 13, 2016 at 5:31 PM, Nikhil wrote: > Hello, > > Is there any documentation with details of how to custom conflict > handlers? Wanted to write update-update conflict handler. Any help is > highly appreciated. > > Best Regards, > Nikhil >
[GENERAL] Custom conflict handlers
Hello, Is there any documentation with details of how to custom conflict handlers? Wanted to write update-update conflict handler. Any help is highly appreciated. Best Regards, Nikhil
[GENERAL] BDR replication
Hello, Is there any way to specify priority for replication. or any parameter which guarantees something about replication (speed at which it replicates, number of minimum replicas to write). Does BDR has a configuration for differentiated services in replication. I want to categorize my data that i replicate. I am ok if some logs are lost in replication. journal entries must not be lost in replication. Best Regards, Nikhil
[GENERAL] BDR with postgres 9.5
Hello All, What is the timeline for BDR with postgres 9.5 released version. Best Regards, Nikhil
[GENERAL] Postgres BDR bdr_init_copy fails
hello , I am trying to setup 2 -node bdr group. i have 5 databases in node 1. i created groups for each db in node-1. then did pg_basebackup from node -2 and started bdr_init_copy. Its giving below error. *bdr_init_copy* bdr_init_copy: starting ... Getting remote server identification ... Detected 5 BDR database(s) on remote server Updating BDR configuration on the remote node: db1: creating replication slot ... db2: creating node entry for local node ... prime: creating replication slot ... prime: creating node entry for local node ... cloud: creating replication slot ... cloud: creating node entry for local node ... stack: creating replication slot ... stack: creating node entry for local node ... dstack: creating replication slot ... dstack: creating node entry for local node ... Creating restore point on remote node ... Bringing local node to the restore point ... pg_ctl: another server might be running; trying to start server anyway Transaction log reset Initializing BDR on the local node: *...hangs after this...* *node-1 pg log* <752812016-01-14 19:46:17 GMT%ERROR: Failed to find expected bdr.connections row (conn_sysid,conn_timeline,conn_dboid) = (6239712043346226652,1,16386) in bdr.bdr_connections <457142016-01-14 19:46:17 GMT%LOG: worker process: bdr (6238458855251125696,1,16386,)->bdr (6239712043346226652,1, (PID 75281) exited with exit code 1 <457142016-01-14 19:46:18 GMT%LOG: starting background worker process "bdr (6238458855251125696,1,16396,)->bdr (6239712043346226652,1," <752822016-01-14 19:46:18 GMT%ERROR: Failed to find expected bdr.connections row (conn_sysid,conn_timeline,conn_dboid) = (6239712043346226652,1,16387) in bdr.bdr_connections <457142016-01-14 19:46:18 GMT%LOG: worker process: bdr (6238458855251125696,1,16396,)->bdr (6239712043346226652,1, (PID 75282) exited with exit code 1 *node-2 pg log* <10.102.31.228(57420)postgres714142016-01-14 19:41:00 GMTmpsdb%LOG: duration: 1.092 ms <10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG: statement: DELETE FROM pg_catalog.pg_ shseclabel WHERE provider = 'bdr'; <10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG: duration: 2.072 ms <10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG: statement: SELECT pg_catalog.pg_repli cation_identifier_drop(riname) FROM pg_catalog.pg_replication_identifier; <10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG: duration: 0.693 ms <10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG: statement: DELETE FROM pg_catalog.pg _shseclabel WHERE provider = 'bdr'; <10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG: duration: 2.350 ms <10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG: statement: SELECT pg_catalog.pg_repl ication_identifier_drop(riname) FROM pg_catalog.pg_replication_identifier; <10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG: duration: 0.672 ms <10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG: statement: DELETE FROM pg_catalog .pg_shseclabel WHERE provider = 'bdr'; <10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG: duration: 2.094 ms <10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG: statement: SELECT pg_catalog.pg_r eplication_identifier_drop(riname) FROM pg_catalog.pg_replication_identifier; <10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG: duration: 0.758 ms <10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG: statement: DELETE FROM pg_catalo g.pg_shseclabel WHERE provider = 'bdr'; <10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG: duration: 1.954 ms <10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG: statement: SELECT pg_catalog.pg_ replication_identifier_drop(riname) FROM pg_catalog.pg_replication_identifier; <10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG: duration: 0.669 ms <713942016-01-14 19:41:00 GMT%LOG: received smart shutdown request <714012016-01-14 19:41:00 GMT%LOG: autovacuum launcher shutting down <713942016-01-14 19:41:00 GMT%LOG: worker process: bdr supervisor (PID 71404) exited with exit code 0 <713942016-01-14 19:41:00 GMT%LOG: unregistering background worker "bdr supervisor" <713982016-01-14 19:41:00 GMT%LOG: shutting down <713982016-01-14 19:41:00 GMT%LOG: database system is shut down
[GENERAL] BDR and synchronous replication
Hello, i am experimenting BDR project. As BDR does asynchronous replication, i have a query regarding bdr.synchronous_commit=on option. Will aforementioned configuration in postgresql.conf makes the replication synchronous? Does this require any other setting? any side effect for using this setup? Best Regards, Nikhil
[GENERAL] To increase RAM or not
Folks, I have set about 12GB RAM (shared buffers) for our Postgresql instance. How do I know if this is actually being used? And is there a way to know by how much should I increase it, if it is not enough? Thanks. -- 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] PSA: If you are running Precise/12.04 upgrade your kernel.
Folks, This is bad news as I run Ubuntu 12.04 LTS. However, my ubuntu 12.04 LTS boxes have been updated to "3.5.0-32-generic" (official update). Any idea whether the Postgresql has problems with this kernel? I'd like to follow the "official" LTS updates because I am not sure what other surprises I could face if I move to an unofficial one. Thanks! Nikhil On 07-06-2013 04:18, Scott Marlowe wrote: On Thu, Jun 6, 2013 at 4:35 PM, Joshua D. Drake wrote: Hello, I had the distinct displeasure of staying up entirely too late with a customer this week because they upgraded to 12.04 and immediately experienced a huge performance regression. In the process they also upgraded to PostgreSQL 9.1 from 8.4. There were a lot of knobs to change/fix/modify because of this. However, nothing I did fixed the problem. Until... I upgraded the kernel. Upgrading from 3.2Precise to the 3.9.4 kernel produced the following results: I've since heard that 3.4 also fixes this issue as well. What are you using for your IO on these boxes? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to check if Postgresql files are OK
Folks, I was using PostgreSQL 8.x in development environment when one day I started getting all kinds of low-level errors while running queries and eventually had to reinstall. Maybe it was salvageable but since it was a test database anyway it didn't matter. We use PostgreSQL 9 on our production server and I was wondering if there there is a way to know when pages get corrupted. I see that there is some kind of checksum maintained from 9.3 but till then is there a way to be notified quickly when such a thing happens? I use a basebackup+rsync of WAL files as a disaster recovery solution. Will this be useful when such a scenario occurs? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to know the exact time to which the database was recovered
Hello, We use the basebackup + WAL files strategy to backup our database i.e. take a basebackup every day and copy WAL files to a remote server every 15 minutes. In case of a disaster on the master, we'd recover the database on the slave. If this happens, I would like to tell the customer the exact time till when the database was recovered. How do I get this timestamp? Thanks. Nikhil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error while vacuuming table
Hello, When I run vacuum, I get: vacuumdb: vacuuming of database "pm50" failed: ERROR: catalog is missing 1 attribute(s) for relid 4210163 How do I go about debugging this? I have googled around and tried a few things to no avail. Any pointers will be appreciated. Thanks. Nikhil -- 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] PostgreSQL Performance issue
Most likely you are inserting one per transaction. Set autocommit to false and commit only after all the inserts are done. -n. On 27-04-2010 13:41, a.bhattacha...@sungard.com wrote: Dear All Experts, I am using in PostgreSQL 8.3.5 database on windows 64 bit OS. However, I have a batch program written in Java which processes the data and populates them into tables in Postgres database. I have *622,000 number of records *but it is taking almost *4 and half hours* to load these data into the tables. I have a simple function in db which is being called from Java batch program to populate the records into tables from flat files. I have the below system configuration for my database server. Database Server *PostgreSQL v8.3.5* Operating System *Windows 2003 Server 64 bit, Service Pack 2* CPU *2 * Quad Core AMD Opteron Processor 2376 @ 2.29 GHz* Memory *16 GB RAM* Disk Space *total 2.5 TB [C drive -- 454 GB & D drive 1.99 TB]* and I have set my postgresql.conf parameters as below. == #-- # RESOURCE USAGE (except WAL) #-- # - Memory - shared_buffers = 1GB temp_buffers = 256MB max_prepared_transactions = 100 work_mem = 512MB maintenance_work_mem = 512MB # - Free Space Map - max_fsm_pages = 160 max_fsm_relations = 1 ' #-- # WRITE AHEAD LOG #-- wal_buffers = 5MB# min 32kB checkpoint_segments = 32 checkpoint_completion_target = 0.9 #-- # QUERY TUNING #-- # - Planner Method Configuration - enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on effective_cache_size = 8GB Please advise me the best or optimum way setting these parameters to achieve better performance. Also note that, when I am setting my *shared_buffer = 2GB or high , *Postgres is throwing an error "/shared_buffer size cannot be more than size_t/" It would be very grateful, if anyone can help me on this. Many thanks
[GENERAL] Postgresql on EC2/EBS in production?
Folks, I was wondering if any of you are using (or tried to use) PG+EC2/EBS on a production system. Are any best-practices. Googling didn't help much. A few articles I came across scared me a bit. Thanks. -- 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] "1-Click" installer problems
On 07-04-2010 10:04, Craig Ringer wrote: Nikhil G. Daddikar wrote: I have tried earlier versions of 8.4 installer on Vista x32 and they'd failed too. But let me try the latest installer again. I will let you know. Any luck with the call? Or are we still at "it doesn't work on your systems or some others but does on many, and we don't know why yet"? Yes, that's the current state. They found that the problem was with the initcluster.vbs file and gave me a new file to run manually. I did and it worked. They then gave me an updated installer with this new file. However, it failed again in the same file. I ran that same file manually again and it worked. Apparently there is a problem creating a 'FileSystemObject' from the installer. We are going to have a webex session in a couple of hours where their engineers will try out various things. I will post the results once I have something concrete. Thanks for your interest. -ngd. -- 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] "1-Click" installer problems
I have tried earlier versions of 8.4 installer on Vista x32 and they'd failed too. But let me try the latest installer again. I will let you know. -n. On 06-04-2010 10:09, Craig Ringer wrote: Nikhil G. Daddikar wrote: Hi Craig, I have a call with EDB folks in an hour. I will post when I have something concrete. Meanwhile, if you need anything specific about the system, please let me know. I have Vista Business x64 Hmm. Your setup is the same as mine except that all the hosts I've tested on are 32-bit installs. Interesting. Unfortunately our friends at Microsoft don't let you simply re-install a 64-bit version, you have to go and buy the OS all over again at absurd retail prices. I don't have an MSDN subscription at work. So I can't personally test with x64 versions. That's certainly something to start looking at, though. I'm sure that if it was as simple as "the installer is broken on x64" it would've turned up by now, but perhaps there's something else that only causes a problem when installs are run on x64 hosts. Something to do with pathname rewriting - Program Files (x86) for example? -- Craig Ringer -- 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] "1-Click" installer problems
Hi Craig, I have a call with EDB folks in an hour. I will post when I have something concrete. Meanwhile, if you need anything specific about the system, please let me know. I have Vista Business x64 with Microsoft Security Essentials and UAC turned ON. I have tried (at least) the following: 1. Default install of PG 8.4 2. Install of PG 8.4 in C:\Postgresql 3. Disable MS Security Essentials for C:\Postgresql and reinstall 4. Create a regular (non-admin) posgres user before installation 5. Delete that user and redo installation 6. Disable UAC and install in C:\Postgresql as well as in the default directory 7. Enabled the "Administrator" user in Vista, logged in as that user and installed If you want me to try anything else, I can do that too. Thanks, Nikhil On 06-04-2010 09:31, Craig Ringer wrote: Nikhil, Any movement on collecting some system comparision information so we can start to figure out why the installer works on my systems, but not on yours? I've just tested on a couple of other Vista systems (at work) without problems, so there must be something different about your systems and those of the others you've seen complain on the EDB forums. It'd be good to find out what it is. -- Craig Ringer
Re: [GENERAL] "1-Click" installer problems
Peter, Are you sure about that? Have you actually tested that? Even if you find that the old 8.3 pginstaller works where the EDB installer failed, that doesn't actually demonstrate that pginstaller is inherently superior to the EDB installer. It is likely due to a fluke. Perhaps it is down to something vestigial from pginstaller remaining. All I am saying is that the folks check out what the diff between EDB and the "old" installer is without jumping to conclusions about APPDATA or whatever because the old installer worked. Maybe it is a fluke but isn't it worth investigating in order to fix the installer? Your incredible sense of entitlement is very irritating. Your unctuous statement that "Evangelizing PGSQL was a mistake", as if you expect a contrite letter, is just too much to bear. You're the one with the big ego. I think that people's responses so far have been very subdued, considering how obnoxious you've been. In your first e-mail, you called the EDB one click installer "a scam". Sometimes we say things that we don't actually mean and this was one of them. I understand I have hurt some folks and I ask forgiveness from all of them. I look forward to working with EDB and help them in fixing the issues. -n. -- 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] "1-Click" installer problems
Folks, I received a call from EnterpriseDB trying to understand what the problem is. We are scheduled to meet on Monday. I will post the findings as soon as I have something concrete. Thank you all. -n. Unless you are prepared to help us understand exactly what is unique about your systems so we can figure out what is going wrong, then we cannot help you. -- 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] "1-Click" installer problems
Maybe I'm missing something, but I see 5 responses from 2 of our engineers over the last 2 days on that thread, all asking valid questions and trying to figure out why this is failing for you, when it does not for the vast majority of other users. I appreciate that you do not want to do a manual installation, but for us to understand why it is failing in your particular case, we need to do some exploration. It is not the count that matters. It is the quality. Even after giving logs, your folks are unable to figure out the problem. I am facing the same problem that was faced by a lot of users on the forum and not one was resolved successfully. Otherwise I would've moved ahead on my own. I am interested in fixing the installer. I am not interested in manual steps to get it working on my PC because there are a hundred other PCs that I have to worry about and a fix in the installer would be right step ahead. But someone has to admit that there is a problem in the installer. All arguments in this thread are junk because 8.3 installer for win32 from postgresql.org WORKS using the same conditions on Vista/2003/7 everywhere. It is nothing to do with APPDATA or any such thing. Something that worked was broken, it's that simple. And nobody wants to know what was. Sachin told you how to install in a nutshell, but I guess you missed that so here are the detail walkthrough instructions: http://www.enterprisedb.com/learning/pginst_guide.do I don't think that's bad for *free* support. If you mean the number of responses, yes it's great. The reason I posted this in the general newsgroup is because I thought others would like to know what's going on. But I think this is a newsgroup with a bunch of inflated egos who want to do everything else rather than address the problem. A typical open-source group. Evangelizing PGSQL was a mistake. Thanks for all your help and good luck to everyone! -n. -- 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] "1-Click" installer problems
On 01-04-2010 12:39, Thomas Kellerer wrote: Nikhil G. Daddikar, 01.04.2010 08:04: In about 30 seconds I found the following unanswered threads relating to installation on Windows Vista. If anybody is interested I can find more. The problem with this kind of statistics is that you will only find people who complain, you'll never find people who do not complain because they have no problems. Actually that's true for all internet forums or mailing lists: you'll seldomly find people posting something like "Hey everything works fine, I had no problems". I agree... but they are still unanswered. And what else can I do. I am facing problems on multiple computers, my customers are facing problems as well. NOBODY till date has managed to install 8.4 on Vista using the 1-click installer and EVERYBODY has managed to install the 8.3 installer (from postgresql.org) on Vista. DOES THIS COUNT AS A VALID STATISTIC? And yes, i have tried installing it in C:\Postgresql as well. Interesting to note that 8.3 installer from postgresql.org installs perfectly in 'C:\Program Files' even on Vista. No use blaming Windows all the time. It is the installer that is buggy. All the posts seem to share the same root cause: the data directory has been put into "c:\Program Files" but a regular user does not have write permissions on that directory. As the installer is usually run with Administrator rights, the directory can be created but the service (or initdb) runs under a normal user account that cannot write to that directory because. I do not like the installer's suggestion to put the data directory into c:\Program Files either, I think this should default to %APPDATA% instead of %ProgramFile%. I bet half of the problems would go away if the installer refused to put the data directory into c:\Program Files. Given the fact that Microsoft finally tries to enforce people not to work as Administrators makes this even more important. My suggestion is to try to use a different data directory when installing Postgres and make sure that the postgres service account is allowed to read and write that directory. Personally I switched to using the ZIP packages completely because it is so much easer (unzip, initdb, pg_ctl -register, done) Thomas -- 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] "1-Click" installer problems
In about 30 seconds I found the following unanswered threads relating to installation on Windows Vista. If anybody is interested I can find more. And I can tell you that a lot of our customers are facing the same problem with this 1-click gimmicky installer.. something that they never faced till the 8.3 installer from postgresql.org. And not everybody is going to post a forum ticket. We've convinced them install 8.3 instead and that works. But their first experience has been bitter and it lingers for a long time. http://forums.enterprisedb.com/posts/list/1815.page http://forums.enterprisedb.com/posts/list/1132.page http://forums.enterprisedb.com/posts/list/2175.page http://forums.enterprisedb.com/posts/list/2033.page http://forums.enterprisedb.com/posts/list/1186.page http://forums.enterprisedb.com/posts/list/1605.page http://forums.enterprisedb.com/posts/list/1578.page -- 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] "1-Click" installer problems
Folks, Here is my original ticket. It has the screen shot as well as the logs. Like many other threads on this forum it remains unresolved. http://forums.enterprisedb.com/posts/list/2235.page If you want to know the number of people who complain about this one-click installer, just search that forum. The reason I posted in this general news group is to make other people aware of the problems that are being encountered. And I am not the kind of person who will complain without reasons. I have spent at least 3 man days trying to install the one-click installer on multiple Vista machines and every time without success... trying to find out the problem so that I could post the solution to the forum. I have been using PG since the last 10 years and we think we do a pretty good job of it. We have successfully converted people from SQL Server and Oracle to PG and I think that in itself qualifies as some kind of "giving back" to PG. I don't like Windows and I don't like Linux and I don't like Mac. But they are reality and in my opinion we need to live with it. If PG will not support Windows Vista or 7, then it should simply say so and I will keep my mouth shut like I did before 8.0. But to move from something that works i.e PG 8.3 win32 binary to something that doesn't i.e the 1Click installer and then claiming that Windows sucks doesn't go down well with me. I appreciate the volunteers that have made the database and the installer till now. What I would like from EDB is a list of instructions on how to install it on Vista or Windows 7. I've asked this in the forum but I have not received any response. Is this too much to ask? -Nikhil PS: It is the users that make or break the software not its developers. So please think twice before asking users to go to hell. Other users might think it will be their turn to hear this some day. On 01-04-2010 09:41, Craig Ringer wrote: Joshua D. Drake wrote: If EDB was failing for a long time, we would hear a lot more about it. They host the most downloaded installer .Org has. True. And sorry for grumping. I've had the misfortune to maintain a win32 installer (in my own time) before, so this is a bit of a hot button. Sure, it works on every system I have access to without issues, but because your FarkWare 4000 Super Privacy Nuker ZX software breaks everything that tries to set file system permissions, the installer's clearly defective. Argh. I still shouldn't let it get to me. My apologies to Nikhil. In any case, while I the EDB installer works extremely well for the clear majority, like anything it could use improvement, and detailed constructive feedback on what exactly needs improvement can only be a good thing. It's the *detailed* bit that needs work right now, though... ( For example, people _do_ get confused by its handling of the postgres service account, especially during reinstall, and it'd be interesting to see if there were other approaches taken by other software that avoided needing to involve the user in working with the postgres service account password directly. ) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "1-Click" installer problems
Hi, I have been trying to install 8.4 on my Vista without success. Earlier I'd tried to install 8.3 and that failed too. I tried installing these on other Vista machines and they failed too. I searched for google and enterprisedb forums and there are innumerable requests like mine. Same with Windows 7. It seems enterprisedb has no clue on what the problem is and how to fix it. There is not even a guide on how to install postgresql on Vista or Windows 7. Their 'guide' is only for MacOS. The "one click" feels like a scam. Search for other threads on their forums or on google. Their support staff want to be helpful but I think they just lack the necessary expertise in solving such issues. I was able to install 8.3 from postgresql.org download site and now I see 8.4 is no longer supported. What are we windows users supposed to do? Our production servers are linux based but our development environment is Windows. I have nothing against enterprisedb but the decision to 'leave out' windows from postgresql.org download site seems political and will simply alienate more people from postgresql. We advocate pgsql to all our customers and help them in installation and training at no cost because we believe that is some way of giving back. With the new "1-click" we are not sure any more. I strongly recommend that you start supporting the win32 binary on postgresql.org site again because that used to work till 8.3 and enterprisedb is failing for a long time. Regards, Nikhil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Segementation fault in PQgetvalue()
This is what I'm trying to: char lmt_str[100]; sprintf(lmt_str,"%s",PQgetvalue(res, 0, nFields-1)); //the last field is of type timestamp This is giving segmentation fault. I'm confused -- 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] Reliability Stability of PgSQL & it's JDBC driver
Nathan, Thanks for your response. It is great to know that Postgres is working out fine. However, I don't understand what "Vaccum of the DB" means nor what the "WAL" functionality is. If you could briefly explain these, that would be great. Thanks again! -ngd. > The main problem that you have in a production system is the VACUUM of the database. > This will in most cases severely inhibit performance. If your system isn't > 24x7, then you should be fine as you can schedule this for off times. I > believe that the need to vacuum the database will be removed in the next > version due to WAL functionality, but I'm sure someone else is better > qualified to explain that. I just wanted to vouch for the stability of > Postgres in a production environment.
