Re: [GENERAL] Event Trigger question

Oh duh, I'm blind... Thanks! On 07/13/2017 07:29 PM, David G. Johnston wrote: On Thursday, July 13, 2017, ProPAAS DBA <mailto:d...@propaas.com>> wrote: 2) where can I find a complete list of the tg_ variables? I see this list: https://www.postgresql.org/docs/9.4/

[GENERAL] Event Trigger question

Hi All; we are creating an event trigger on ddl_command_end and I want the function to know the TABLE and COMMAND run, for example if the ddl command was an "ALTER TABLE ADD COLUMN X" then I want to pull the table and the actual alter command. We're running version 9.4 so the pg_event_trigge

Re: [GENERAL] psql only works with -h (even localhost)

On 01/25/2017 09:35 AM, Tom Lane wrote: Steve Crawford writes: Adrian asks the correct questions. Lacking the answers to those I'm going to venture a guess that a Unix-domain socket exists but access via Unix-domain sockets is somehow blocked, probably by pg_hba.conf. Actually, the more comm

[GENERAL] psql only works with -h (even localhost)

Hi all; we have a client server where 'psql -h localhost' is the only way psql will connect when ssh'd onto the db server. Would like to be able to just run psql but not sure what the issue/fix is. Anyone have any insight hot to fix this? Thanks in advance -- Sent via pgsql-general ma

[GENERAL] raise notice question

Is it possible to execute a raise notice without the "Notice" keyword being part of the output, or is there another construct that allows writing to output as simple informational text (without any sort of NOTICE, WARNING, etc being part of the output? Thanks in advance -- Sent via pgsql

[GENERAL] Re: [ADMIN] Would like to below scenario is possible for getting page/block corruption

Hi Sreekanth, I doubt auto-recover of the page might be possible, as the header of the page is no more valid & corrupted and not sure whether the corruption occurred in relation of a data or index block. We have seen some occurrences like this before which got rectified by performing reindexing a

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] 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 )

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 )

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

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

[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

[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

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

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] 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

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] 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/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 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

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>&

[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

[GENERAL] "PostgreSQL" Version 9.3 Supportability

Hi Team, We are planning to migrate few Vendor based applications from Windows Server 2003 to the latest server Operating System Windows Server 2012/2008. Before migration we would like to do the assessment of the application and check the supportability. We are performing the assessment for: Ap

[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] 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

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] 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] 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] 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

[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

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] 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

[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

[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

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] 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

[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

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] 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

[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] 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

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

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

[GENERAL] " FATAL: no pg_hba.conf entry for host" messages...

AL messages all of a sudden and is repetitive.. Can someone kindly advise? As I am primarily an Oracle DBA - I would rather request a detailed issue cause and resolution to talk to the customer, before hand. Thanks for your help in advance! Best, Shafi Ahmed IS Sify INDIA &qu

[GENERAL] Is there any provision to take incremental backup

Is there any provision to take incremental backup for a table in postgresql. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Is-there-any-provision-to-take-incremental-backup-tp4285873p4285873.html Sent from the PostgreSQL - general mailing list archive at Nabble.com

[GENERAL] using warm standby with drbd???

Hi All! I have configured the warm standby using WAL FILES and DRBD. The wal files are generated as server A. I am using DRBD to replicate these wal files between server A and server B. The DRBD service is running as Primary on Server A and secondary on Server B. The wal_files are written to the s

RE: [GENERAL] problem of upper/lower case in table names

On Tue, 17 Nov 1998, Taral wrote: > > >i've got this annoying problem : if you create a table with an uppercase > > >name, postgres transforms it in lower case. After that, if you try to > > >retrieve the primary keys for this table ( still using the uppercase > > >name as argument ) using the

[GENERAL] cyrillic recode support

Hi All! Compiling Postgres-6.4 I came accross following option in ./configure script: " --enable-recode enable cyrillic recode support" What is it for ? I wonder if it's intended to add capability of translation between koi8 and win-1251 encofing, but I was unable to find in manuals and

Re: [GENERAL] Case insensitive "contains" search

hi! I'm not shure that syntax you have used is correct, I mean that better is: select * from tablex where name LIKE '%test%'; or you can also try "~~" operator that is used in Postgres as synonym to "LIKE": select * from tablex where name ~~ '%test%'; Regarding your second example: select * fro