[GENERAL] Getting UDR up and running

Hi All; 2 questions about UDR: 1) is there documentation showing how to get a one way node (UDR) up and running? 2) Is it possible to push to a node one way (via UDR) from a BDR node (i.e. a set of BDR nodes for H/A and a single UDR node to serve up replication one way as the source for a DSS/Re

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

On 07/08/2015 02:20 PM, John R Pierce wrote: > On 7/8/2015 1:16 PM, dinesh kumar wrote: >> We recently done the similar migration for one of our customer. We >> used all opensource tools to achieve this migration process. >> >> We used Pentaho Data Integration tool for doing Online DB migration,

[GENERAL] xmin horizon?

All; The documentation for pg_stat_activity lists this column: backend_xmin xid The current backend's xmin horizon. Can someone point me to a better understanding on "xmin horizon"? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

[GENERAL] WAL Shipping and streaming replication

All; We have a 3 node replication setup: Master (node1) --> Cascading Replication Node (node2) --> Downstream Standby node (node3) We will be deploying WAL archiving from the master for PITR backups and we'll use the staged WAL files in the recovery.conf files in case the standbys need to

Re: [GENERAL] plql and or clausule

Try this: CREATE OR REPLACE FUNCTION lst_tot_mytable_LOG() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO lst_tot_mytable_log SELECT 'U', now(), OLD.*; IF OLD.Peticionario != NEW.Peticionario or OLD.interlocclte != NEW.interloc

Re: [GENERAL] Log all queries before migration ?

log_min_duration_statement = 0 On 07/29/2016 05:35 AM, ben.play wrote: Hi guys, We have some storage problem on our dedicated server and we have to migrate. Do you know how can I log all queries in order to have no downtime during migration ? I tried many options on the conf, but i never fin

[GENERAL] json select question

All; I'm working with a client running postgres 9.2, they have a table with a "json_data_string" column of type json the data looks something like this with lots of rows for each (i.e. lots of json_data_string->book_name rows, lots of json_data_string->catalog_name rows, etc: |'{ "book_n

[GENERAL] checkpoint write errors

Hi all; we're seeing the below errors over and over in the logs of one of our postgres databases. Version 8.4.22 Anyone have any thoughts on correcting/debugging it? Maybe I need to run a REINDEX on whatever table equates to "base/1029860192/1029863651"? If so how do I determine the db and

Re: [GENERAL] checkpoint write errors

Thanks the REINDEX fixed it, it's a client of ours and we're pushing to get them to move to 9.5 On 10/21/2016 06:33 PM, Tom Lane wrote: CS DBA writes: we're seeing the below errors over and over in the logs of one of our postgres databases. Version 8.4.22 [ you really oug

Re: [GENERAL] checkpoint write errors ( getting worse )

311892067/2687: read only 0 of 8192 bytes Thoughts? On 10/22/2016 07:52 AM, CS DBA wrote: Thanks the REINDEX fixed it, it's a client of ours and we're pushing to get them to move to 9.5 On 10/21/2016 06:33 PM, Tom Lane wrote: CS DBA writes: we're seeing the below errors

Re: [GENERAL] checkpoint write errors ( getting worse )

would a dump/restore correct these issues? On 10/22/2016 05:59 PM, Tom Lane wrote: CS DBA writes: So I ran REINDEX on all the db's and the errors went away for a bit. Now I'm seeing this: Log entries like this:FATAL: could not read block 0 of relation base/1311892067/2687: read

Re: [GENERAL] checkpoint write errors ( getting worse )

also, any thoughts on what could be causing these issues? On 10/22/2016 05:59 PM, Tom Lane wrote: CS DBA writes: So I ran REINDEX on all the db's and the errors went away for a bit. Now I'm seeing this: Log entries like this:FATAL: could not read block 0 of relation base/13118

Re: [GENERAL] checkpoint write errors ( getting worse )

tling limiting the server to no more than 2 cpu's On 10/23/2016 10:53 PM, Michael Paquier wrote: On Sun, Oct 23, 2016 at 12:45 PM, CS DBA wrote: would a dump/restore correct these issues? Not directly, but it would give a logical representation of your data, or a good start image that

Re: [GENERAL] XID wraparound with huge pg_largeobject

On 12/02/2015 09:36 AM, David Kensiski wrote: On Tue, Dec 1, 2015 at 1:48 PM, Roxanne Reid-Bennett > wrote: On 11/30/2015 9:58 AM, David Kensiski wrote: I am working with a client who has a 9.1 database rapidly approaching XID wraparound. They also have a

[GENERAL] Exporting a PDF from a bytea column

Hi all; we have inherited a legacy database. The system stores PDF's as large objects in bytea columns. Can anyone send me an example of exporting from a bytea column to a PDF file? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to yo

Re: [GENERAL] Exporting a PDF from a bytea column

On 02/18/2016 07:29 PM, David G. Johnston wrote: On Thursday, February 18, 2016, John R Pierce <mailto:pie...@hogranch.com>> wrote: On 2/18/2016 4:44 PM, CS DBA wrote: The system stores PDF's as large objects in bytea columns. Large Objects aka

[GENERAL] decoding BLOB's

Hi All; we've found a post about dumping blobs: / / /I wrote: [fanlijing wants to write bytea to file] > A simple > COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > should do the trick. Corrections: a) "binary" must be surrounded by single quotes. b) *that won't dum

[GENERAL] Oracle conversion questions - TYPE's and ARRAY's

All; We are running an Oracle conversion via ora2pg the TYPE's come out like this: CREATE OR REPLACE TYPE "CUST_ARRAY_TBL" as table of varchar2(4000); or CREATE OR REPLACE TYPE "STRTAB" as table of varchar2(4000 char); The sqlplus code wants to use these types as array's like this: BEGIN

[GENERAL] Partitioning and ORM tools

Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into the partitioned table which causes the ORM tool to assume the insert inserted 0 rows. Is there a standard / best practices work around fo

Re: [GENERAL] Partitioning and ORM tools

help you. Useful and needed information would be: 1. Version of PostgreSQL 2. Operating System 3. Table structure for partitioned table 4. Trigger function and trigger used for insert 5. The actual insert statement. On Tue, Mar 22, 2016 at 2:40 PM, CS DBA <mailto:cs_...@consistentstate.com>&

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 02:23 PM, Joshua D. Drake wrote: On 03/22/2016 11:40 AM, CS DBA wrote: Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into the partitioned table which ca

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 02:43 PM, Joshua D. Drake wrote: On 03/22/2016 01:35 PM, CS DBA wrote: On 03/22/2016 02:23 PM, Joshua D. Drake wrote: On 03/22/2016 11:40 AM, CS DBA wrote: Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSER

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 03:18 PM, Rob Sargent wrote: On 03/22/2016 03:00 PM, Joshua D. Drake wrote: On 03/22/2016 01:50 PM, CS DBA wrote: Understood, was just wondering if there is a way to cause the child table insert results to be returned to the ORM/Application instead of the master/base table

Re: [GENERAL] Partitioning and ORM tools

On 03/23/2016 02:48 AM, Chris Travers wrote: On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers mailto:chris.trav...@gmail.com>> wrote: Use a view with a DO INSTEAD trigger. That will allow you to return the tuple properly. On Tue, Mar 22, 2016 at 7:40 PM, CS DBA mail

Re: [GENERAL] Fastest way to duplicate a quite large database

On 04/13/2016 08:46 AM, Edson Richter wrote: Em 13/04/2016 11:18, Adrian Klaver escreveu: On 04/13/2016 06:58 AM, Edson Richter wrote: Another trouble I've found: I've used "pg_dump" and "pg_restore" to create the new CustomerTest database in my cluster. Immediately, replication started to

[GENERAL] recovering from "too many failures" wal error

All; We have a postgresql 9.2 cluster setup to do continuous wal archiving. We were archiving to a mount point that went offline. As a result the db could not archive the wal files, we ended up with many many errors in the logs indicating the file could not be archived: WARNING: transaction

Re: [GENERAL] Call for design: PostgreSQL mugs

On 9/3/13 4:08 PM, Andreas 'ads' Scherbaum wrote: PostgreSQL folks! We are looking for the next big thing. Actually, it's a bit smaller: a new design for mugs. So far we had big blue elephants, small blue elephants, frosty elephants, white SQL code on black mugs ... Now it's time to design s

Re: [GENERAL] How to switch file systems with least downtime?

We've done this across servers, maybe it would work for you: 1) rsync the entire db to the second file system 2) do another rsync each day until you are ready to swap (of course only changed files will be moved) 3) before the swap do another rsync to bring the db as close to sync'ed as possi

[GENERAL] PostgreSQL vs Mongo

All; One of our clients is talking about moving to Mongo for their reporting/data mart. I suspect the real issue is the architecture of their data mart schema, however I don't want to start pushing back if I can't back it up. Anyone have any thoughts on why we would / would not use Mongo fo

[GENERAL] Add PK constraint to a Unique Index via updating system catalogs?

Hi All; We have a few very large tables with unique indexes on a column but the column is not defined as the Primary Key. Can we add a PK constraint via updates to the system catalogs so we can avoid the lengthy checks that would take place if we ran "alter table add constraint primary key...

[GENERAL] general questions

Hi All; I recently ran into the following, any thoughts? Thanks in advance... 1) \d and schema's - I setup 2 schema's (sch_a and sch_b) - I added both schema's to my search_path - I created 2 tables: sch_a.test_tab and sch_b.test_tab If I do a \d with no parameters I only see the first test_t

Re: [GENERAL] Add PK constraint to a Unique Index via updating system catalogs?

Any way to add a PK "under the covers" for PostgreSQL version 8.3? On 01/06/2014 03:53 PM, Thomas Kellerer wrote: CS DBA wrote on 06.01.2014 23:30: We have a few very large tables with unique indexes on a column but the column is not defined as the Primary Key. Can we add a PK

[GENERAL] HOT Standby & SSL

All; How do I force a standby to connect to the master via ssl only? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] JDBC performance issue

Hi All; We have a client running Pentaho to migrate data. They recently upgraded Pentaho which upgraded their JDBC driver from 8.4 (postgresql-8.4-703.jdbc4.jar) to 9.1 (postgresql-8.4-703.jdbc4.jar). They have a test set which updates 1000 rows, with the old driver it takes about 30 seconds

Re: [GENERAL] designing time dimension for star schema

I've done a lot of DSS architecture. A couple of thoughts: - in most cases the ETL process figures out the time id's as part of the preparation and then does bulk loads into the fact tables I would be very concerned about performance of a trigger that fired for every row on the fact table

[GENERAL] Hardware performance

Hi All; We're talking with a HW / Data Center company about scaling up our DB servers... Below are some questions they asked relaed to moving to SSD's or maybe a Fusion IO drive. Anyone have any thoughts, specifically on the queue depth question? Thanks in advance... /So our question I thi