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

[CS DBA]

Understood, thanks. This is a new server fired up for our client by 
Rackspace


Not real impressed so far, for the first several days we had major 
performance issues even thought new new HW had more memory and 
more/faster CPU's and faster IO - turned out rackspace had turned on cpu 
throttling 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 you could deploy on a server that has less
problems. You seem to be facing advanced issues with your hardware
here.




--
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] checkpoint write errors ( getting worse )

[CS DBA]

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/1311892067/2687: read only 0 of 8192 bytes

You have a problem there, because:

regression=# select 2687::regclass;
regclass
--
  pg_opclass_oid_index
(1 row)

which is a pretty critical index.

You might be able to fix this by starting a single-user backend with -P
(--ignore-system-indexes) and using it to REINDEX that index.

On the whole, though, it's starting to sound like that system has
got major problems.  You'd be well advised to focus all your efforts
on getting a valid dump, not bringing it back into production.

regards, tom lane




--
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] checkpoint write errors ( getting worse )

[CS DBA]

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 only 0 of 8192 bytes

You have a problem there, because:

regression=# select 2687::regclass;
regclass
--
  pg_opclass_oid_index
(1 row)

which is a pretty critical index.

You might be able to fix this by starting a single-user backend with -P
(--ignore-system-indexes) and using it to REINDEX that index.

On the whole, though, it's starting to sound like that system has
got major problems.  You'd be well advised to focus all your efforts
on getting a valid dump, not bringing it back into production.

regards, tom lane




--
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] checkpoint write errors ( getting worse )

[CS DBA]

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 only 0 of 8192 bytes



So I checked which db it is:

$ psql -h localhost
psql (8.4.20)
Type "help" for help.

postgres=# select datname from pg_database where oid = 1311892067;
 datname
-
 access_one
(1 row)

But when I attempt to connect to the db so I can query for the table in 
pg_class I get this:


postgres=# \c access_one
FATAL:  could not read block 0 of relation base/1311892067/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 over and over in the logs of one of our
postgres databases. Version 8.4.22

[ you really oughta get off 8.4, but you knew that right? ]


Anyone have any thoughts on correcting/debugging it?
ERROR:  xlog flush request 2571/9C141530 is not satisfied --- flushed
only to 2570/DE61C290
CONTEXT:  writing block 4874 of relation base/1029860192/1029863651
WARNING:  could not write block 4874 of base/1029860192/1029863651
DETAIL:  Multiple failures --- write error might be permanent.
Evidently the LSN in this block is wrong.  If it's an index, your 
idea of

REINDEX is probably the best solution.  If it's a heap block, you could
probably make the problem go away by performing an update that 
changes any
tuple in this block.  It doesn't even need to be a committed update; 
that

is, you could update or delete any row in that block, then roll back the
transaction, and it'd still be fixed.

Try to avoid shutting down the DB until you've fixed the problem,
else you're looking at replay from whenever the last successful
checkpoint was :-(


Maybe I need to run a REINDEX on whatever table equates to
"base/1029860192/1029863651"?  If so how do I determine the db and 
table

for "base/1029860192/1029863651"?

1029860192 is the OID of the database's pg_database row.
1029863651 is the relfilenode in the relation's pg_class row.

regards, tom lane

Re: [GENERAL] checkpoint write errors

[CS DBA]

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 oughta get off 8.4, but you knew that right? ]


Anyone have any thoughts on correcting/debugging it?
ERROR:  xlog flush request 2571/9C141530 is not satisfied --- flushed
only to 2570/DE61C290
CONTEXT:  writing block 4874 of relation base/1029860192/1029863651
WARNING:  could not write block 4874 of base/1029860192/1029863651
DETAIL:  Multiple failures --- write error might be permanent.

Evidently the LSN in this block is wrong.  If it's an index, your idea of
REINDEX is probably the best solution.  If it's a heap block, you could
probably make the problem go away by performing an update that changes any
tuple in this block.  It doesn't even need to be a committed update; that
is, you could update or delete any row in that block, then roll back the
transaction, and it'd still be fixed.

Try to avoid shutting down the DB until you've fixed the problem,
else you're looking at replay from whenever the last successful
checkpoint was :-(


Maybe I need to run a REINDEX on whatever table equates to
"base/1029860192/1029863651"?  If so how do I determine the db and table
for "base/1029860192/1029863651"?

1029860192 is the OID of the database's pg_database row.
1029863651 is the relfilenode in the relation's pg_class row.

regards, tom lane




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] checkpoint write errors

[CS DBA]

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 table 
for "base/1029860192/1029863651"?



LOG:  checkpoint starting: time
ERROR:  xlog flush request 2571/9C141530 is not satisfied --- flushed 
only to 2570/DE61C290

CONTEXT:  writing block 4874 of relation base/1029860192/1029863651
WARNING:  could not write block 4874 of base/1029860192/1029863651
DETAIL:  Multiple failures --- write error might be permanent.


Thanks in advance

[GENERAL] json select question

[CS DBA]

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_name": "Book the Fourth", "author": { "first_name": "Charles", 
"last_name": "Davis" } }' ||'{ "catalog_name": "Catalog the Fourth", "author": { "first_name": 
"Charles", "last_name": "Davis" } }'|
|'{ "magazine_name": "mag4", "author": { "first_name": "Charles", 
"last_name": "Davis" } }' |||'{ "category_name": "region", "author": { "first_name": "Charles", 
"last_name": "Davis" } }'||



How can i pull a unique list of all json column names? such as book_name, 
catalog_name, etc

Thanks in advance

Re: [GENERAL] Log all queries before migration ?

[CS DBA]

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 find the good one. I didn't
find out to generate .sql file with all queries.

Thank you a lot for your help !




--
View this message in context: 
http://postgresql.nabble.com/Log-all-queries-before-migration-tp5913922.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

Re: [GENERAL] plql and or clausule

[CS DBA]

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.interlocclte
or OLD.Equipo != NEW.Equipo
or OLD.RespTecnico != NEW.RespTecnico
or OLD.RespOrganiz != NEW.RespOrganiz THEN
UPDATE lst_tot_mytable set fultimamodificacion = now(),
esmodificadoerspectoaanterior = true
WHERE nropeti = OLD.nropeti;
ELSIF OLD.Peticionario = NEW.Peticionario
or OLD.interlocclte = NEW.interlocclte
or OLD.Equipo = NEW.Equipo
or OLD.RespTecnico = NEW.RespTecnico
or OLD.RespOrganiz = NEW.RespOrganiz THEN
UPDATE lst_tot_mytable set NEW.fultimamodificacion = now(),
NEW.esmodificadoerspectoaanterior = fase
WHERE nropeti = OLD.nropeti;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$;



However if I get your purpose I suspect the ELSIF section may need to be 
AND's:



ELSIF OLD.Peticionario = NEW.Peticionario
AND OLD.interlocclte = NEW.interlocclte
AND OLD.Equipo = NEW.Equipo
AND OLD.RespTecnico = NEW.RespTecnico
AND OLD.RespOrganiz = NEW.RespOrganiz THEN




On 05/31/2016 03:18 PM, car...@lpis.com wrote:


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.interlocclte or OLD.Equipo != NEW.Equipo or OLD.RespTecnico != 
NEW.RespTecnico or OLD.RespOrganiz != NEW.RespOrganiz THEN
UPDATE lst_tot_mytable set fultimamodificacion = now(), 
esmodificadoerspectoaanterior = true WHERE nropeti = OLD.nropeti;
ELSIF OLD.Peticionario == NEW.Peticionario or OLD.interlocclte == 
NEW.interlocclte or OLD.Equipo == NEW.Equipo or OLD.RespTecnico == 
NEW.RespTecnico or OLD.RespOrganiz == NEW.RespOrganiz THEN
UPDATE lst_tot_mytable set NEW.fultimamodificacion = now(), 
NEW.esmodificadoerspectoaanterior = fase WHERE nropeti = OLD.nropeti;

END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$;




--
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] Fastest way to duplicate a quite large database

[CS DBA]

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 replicate the 60Gb data into slave, causing big
trouble.
Does mark it as "template" avoids replication of that "copied" 
database?

How can I mark a database to "do not replicate"?


With the Postgres built in binary replication you can't, it 
replicates the entire cluster. There are third party solutions that 
offer that choice:


http://www.postgresql.org/docs/9.5/interactive/different-replication-solutions.html 



Table 25-1. High Availability, Load Balancing, and Replication 
Feature Matrix


Thanks, I'll look at that.

It has been mentioned before, running a non-production database on 
the same cluster as the production database is a generally not a good 
idea. Per previous suggestions I would host your CustomerTest 
database on another instance/cluster of Postgres listening on a 
different port. Then all you customers have to do is create a 
connection that points at the new port.


Thanks for the concern.
This "CustomerTest" database is a staging, for customer approval 
before upgrading the production system.
I bet the users will only open the system, and say it is ok. As 
crowded as people are those days, I doubt they will validate something 
that is already validated by our development team.

But our contractor requires, and we provide.
Since we have "express devivery of new versions" (almost 2 per week), 
we would like to automate the staging environment.


Thanks,

Edson





Thanks,

Edson











Have you tried this:

- to copy database named prod to a database named prod_test:

on the same cluster (same server):
pg_dump prod --create --clean| psql prod_test

Copy from prod db cluster to another cluster/server (i.e. the test server)
pg_dump prod --create --clean| psql -h [test_server_ip] prod_test




--
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] Partitioning and ORM tools

[CS DBA]

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
mailto:cs_...@consistentstate.com>>
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 causes
the ORM tool to assume the insert inserted 0 rows.  Is there a
standard / best practices work around for this?


Apologies for the top post above.

Just noting additionally that the view with DO INSTEAD approach was 
suggested to me by Matt Trout (major contributor to the DBIx::Class 
ORM in Perl.


I have used it.  It works well.  I think it is the best practice there.


Thanks in advance




-- 
Sent via pgsql-general mailing list

(pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




-- 
Best Wishes,

Chris Travers

Efficito:  Hosted Accounting and ERP. Robust and Flexible.  No
vendor lock-in.
http://www.efficito.com/learn_more




--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor 
lock-in.

http://www.efficito.com/learn_more


All;

Thanks for the great Ideas, I'll let you know where we end up.

Re: [GENERAL] Partitioning and ORM tools

[CS DBA]

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 insert


Insert into the child table directly based on the partition rules.

JD


I would think the ORM (as yet undefined) would want to think in terms 
of the parent table and not know about the physical schema details.
Can  the client not be written to check only for errors vs checking 
for non-zero inserts?





That was our first suggestion, they don;t want to make any app changes




--
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] Partitioning and ORM tools

[CS DBA]

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: "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 for this?

Thanks in advance


Are you inserting via trigger from an insert into a parent table? That
would do it.



Yes.. that's exactly the issue.


Because the parent table doesn't actually receive the insert, it is 
returning correctly (if obnoxiously considering the circumstances). It 
is known, expected behaviour.


Sincerely,

JD









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 insert





--
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] Partitioning and ORM tools

[CS DBA]

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 causes the ORM tool to assume the
insert inserted 0 rows.  Is there a standard / best practices work
around for this?

Thanks in advance


Are you inserting via trigger from an insert into a parent table? That 
would do it.




Yes.. that's exactly the issue.




--
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] Partitioning and ORM tools

[CS DBA]

On 03/22/2016 01:10 PM, Rob Sargent wrote:



On 03/22/2016 12:55 PM, Melvin Davidson wrote:
Your problem seems strange as it has never been previously reported 
for anyone else that has _successfully_ set up partioning.
Perhaps is you provide just a little bit more detail we might be able 
to 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>> 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 causes the ORM
tool to assume the insert inserted 0 rows.  Is there a standard /
best practices work around for this?

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




And which ORM are you using.

I take it the problem is that the ORM is saying "zero rows inserted" 
and that's um, er, upsetting the client ( which might decide to retry 
and then generates an error for non-unique key or some such noise)


rjs



--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


I think they are using Ruby, turns out the application is checking this 
and throwing an error (and rolling back) when it detects no rows inserted

[GENERAL] Partitioning and ORM tools

[CS DBA]

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 for this?


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] Oracle conversion questions - TYPE's and ARRAY's

[CS DBA]

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

...


myArrList := load_my_type();
myArrList.EXTEND(1);
myArrListSpecific := load_my_cust_arr_type();
myArrListSpecific.EXTEND(1);


IF inv_unpaid = 1 THEN
myArrListSpecific(myArrListSpecific.Last).FieldName := 'CUST_INV_ID';
myArrListSpecific(myArrListSpecific.Last).FieldValue := arr_desc_str;
myArrListSpecific.EXTEND(1);

...


Does anyone have any thoughts per converting the above to postgres and 
PL/pgSQL?


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] decoding BLOB's

[CS DBA]

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 dump just the binary data - you would have 
to remove the first 25 bytes and the last 2 bytes...* So maybe using the 
functions I mentioned would be the best way after all. You could also 
write your own user defined function in C. /




Can someone point me in the right direction per how I would remove the 
first 25 bytes and the last 2 bytes from a bytea column?


Thanks in advance

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

[CS DBA]

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 LO's and bytea columns are two completely
different things.


I'll assume the "column" is the most relevant term here because the 
above is true.


Can anyone send me an example of
exporting from a bytea column to a PDF file?


I don't think you can get from a bytea field to a file without
some coding, as SQL scripting doesn't handle binary blobs very well.


http://www.postgresql.org/message-id/AANLkTi=2darhqprgtxmcx0aus9mahq2sfxxbvihtu...@mail.gmail.com

Short answer, to avoid the binary blob problem, is to encode the 
binary data, export it, then decode it.


This can be done is psql.  If your client can handle binary directly 
(e.g, JDBC/Java) you can use that language's facilities to perform the 
binary transfer directly thus bypassing the need to transcode.
Can it be done from a Linux shell script?  Any examples? Seems to be 
little info on this in my googling?





David J,

[GENERAL] Exporting a PDF from a bytea column

[CS DBA]

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 your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XID wraparound with huge pg_largeobject

[CS DBA]

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 an exceedingly large
pg_largeobject table (4217 GB) that has never been vacuumed.  An
attempt to vacuum this on a replica has run for days and never
succeeded.  (Or more accurately, never been allowed to succeed
because we needed to get the replica back on-line.)
...
Any other ideas about how we can do this?


David,

My gut reaction was maybe dump/restore ... but it's
pg_largeobject.  I have read the list for years and my memory
tells me that it is a problem child in that arena. (e.g. as you
found out w Slony...)  and at 4000Gb, not something that can
sandbox very well.


I attempted to dump the qa system and pipe it to restore on a test 
server and it crashed qa.  Doesn't a dump run as a single transaction, 
so a long running dump on a busy database accrues more and more 
resources that don't get released until it completes?  Or am I missing 
something?


Either way, it means several days of downtime given the size of the 
database.  Not really practical.



Because it's v9.1... and you hadn't gotten any responses (until
Jeff)... and I had the time... I did some digging in the archives...

The most promising alternate idea... Last February Adam Hooper was
migrating to SSD, Bill Moran suggesting trying to CLUSTER
pg_largeobject instead of VACUUM FULL.  (full topic: on 2/3/2015
entitled "VACUUM FULL pg_largeobject without (much) downtime?")

CLUSTER has been referenced in the list other times to collapse
unused space (sometime in 2010-2011):

As a last resort this week, I'm going to get 500+GB of extra file store
added, add a tablespace and move pg_largeobjects to this area. Then use
CLUSTER to rebuild pg_largeobjects back in the default tablespace. This
should fix things I hope, and if needed I'll use Cluster regularly.

This is intriguing and is worth testing to see if it would work.  
Getting the space to do it on production might be tough since all 
drive slots are full and we're already using 3 TB drives.  But worth 
trying in qa and if it works, then I can get creative for prod.


It's "an" other idea...  I've no idea whether it will work any
better than biting the bullet and just running VACUUM FULL.

other bits and pieces...

In 2010, Tom suggested REINDEX then VACUUM on pg_largeobject for
an 8.? system.  That peaked my interest because we found with 9.1
that weekly reindexing helped with performance.  However the
person who used it didn't find any performance improvement with
his VACUUM.  I think reindexing was added to VACUUM FULL in the
9.0 release (but would have to search the release notes to find it).

I remember reading somewhere during this (but can't find the
reference ) that an interrupted VACUUM FREEZE does capture
"some" data, so multiples of those actually incrementally improves
the speed of the next - but again I can't find the reference, so
I've no idea who, when, version, and whether my memory is faulty
or misapplied.


I've seen this as well, and Jeff alluded to it in his post.  
Subsequent vacuums will run faster after a partial vacuum since some 
of the data has already been processed.



There are miscellaneous improvements in the actual running of
VACUUM FULL (and more often autovacuum) suggested through tweaking
the vacuum parameters "vacuum_cost_delay" being a high priority
target. Jeff's questions all point an identifying any limitations
that are costing you time due to configuration.


Definitely be tweaking the parameters to get the best performance if 
we have to go the vacuum route.



Totally not part of this specific problem... You have run or know
of vacuumlo for deleting orphaned LOs...? Might be worth running
it before you collect your free space.  [just in case you didn't
or hadn't... twice the bang, half the pain - but only if you do it
before collecting your free space]


I don't think anyone has tried to vacuumlo, but another one I can test 
in qa.  If it improves vacuum full performance it might be worth doing 
it in two phases.


Thanks to both of you for your input!  (I forgot to thank Jeff in my 
reply to him.)


--Dave





Not sure if it applies in your case (large objects via bytea vs pg built 
in large objects). However, maybe you can partition the table as follows:


1) create a new master table
2) create the needed partitions
3) begin migrating in the background, as you migrate each partition, via 
a select * from current table, you will not be copying any dead rows

4) once all existing data is copied perform the following:
   a) declare outage
   b) copy remaini

[GENERAL] WAL Shipping and streaming replication

[CS DBA]

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 revert to log shipping.


Question:  whats the best way to ensure consistency of WAL archiving in 
the case of changes  (failover, etc)? can we setup the cascade node to 
archive wals only if it's the master? is this a case where we should 
deploy repmgr?


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] xmin horizon?

[CS DBA]

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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[CS DBA]

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,
>> which took minimal downtime with CDC{Change Data Capture} approach.
>> Also, we used Ora2Pg tool to migrate the DB objects with some manual
>> syntax modifications.
>>
>
> thats the easy part.
>
> now what about the massive code base of pl/sql and triggers he
> mentioned ?
>
>
>
Have you considered using ora2pg?
http://ora2pg.darold.net/

We've done several client migrations with it, quite successfully



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Getting UDR up and running

[CS DBA]

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/Reporting node)


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] recovering from "too many failures" wal error

[CS DBA]

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 log file "00010FB10050" could not be 
archived: too many failures


So we caught the issue before the file system filled up, fixed the mount 
point and I see wal files being added to the target wal archive 
directory.  However the pg_xlog directory does not seem to be shrinking, 
there are currently 27,546 files in the pg_xlog directory and that 
number is not changed in some time (since we fixed the mount point.


I assume the db will at some point remove the backed up files in the 
pg_xlog dir, is this true? or do I need to intervene?


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

[CS DBA]

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 think would be:/
//
/
/
//
/- What queue depth do you think your postgres server can saturate under 
your maximum load, when you'd want 35,000 IOPs?/

//
/
/
//
/Basically my concern is this: assuming a ~5ms round trip, which 
probably a top end for a "good" SSD array, you effectively get 200 IOPs 
per queue depth at the full 5ms (1000ms / 5ms for 200 IOPs per QD; so 
1600 IOPS for QD=16)./

//
/
/
//
/That means to get 35k IOPs you'd need a collective QD=175./
//
/
/
//
/An additional question would be, and Lou already touched on this:/
//
/
/
//
/- Are you comfortable with being presented multiple LUNs and striping 
them for your database filesystem? I'm not 100% sure about the per-LUN 
limits of the EMC offerings which we've started looking into, but 
SolidFire tends to max out around 17k IOPs per volume, for example; they 
have a stated maximum of 15k, and when the array is under full load it's 
more realistic to only expect about 8k. So to get 35k, we'd probably 
want to present multiple volumes to the OS and let it stripe them (no 
parity since the durability would be on the array)./

//
/
/
//
/A related question would be:/
//
/
/
//
/- As you migrate away from RHCS, how do you feel about utilizing local 
SSDs to meet your performance targets? If your performance 
characteristics around your queue depth don't fit well with an array, 
the lower latency to a local SSD might make it easier to sustain 35k 
IOPs with local RAID SSDs./

//
/
/

Re: [GENERAL] designing time dimension for star schema

[CS DBA]

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


 you mention you want to do data streaming instead of bulk loads, 
can you elaborate?



- When querying a star schema one of the performance features is the 
fact that all joins to the dimension tables are performed via a numeric 
key, such as:

"select * from fact, time_dim, geo_dim
  where fact.time_id = time_dim.time_id..."

In the case of this being a timestamp I suspect the performance would 
take a hit, depending on the size of your fact table and the 
scope/volume of your DSS queries this could easily be a show stopper 
based on the assumption that the database can do a numeric binary search 
much faster than a timestamp search






On 2/10/14, 9:45 AM, Mark Wong wrote:

Hello everybody,

I was wondering if anyone had any experiences they can share when
designing the time dimension for a star schema and the like.  I'm
curious about how well it would work to use a timestamp for the
attribute key, as opposed to a surrogate key, and populating the time
dimension with triggers on insert to the fact tables.  This is
something that would have data streaming in (as oppose to bulk
loading) and I think we want time granularity to the minute.

A simplified example:

-- Time dimension
CREATE TABLE time (
 datetime TIMESTAMP WITH TIME ZONE NOT NULL,
 day_of_week SMALLINT NOT NULL
);
CREATE UNIQUE INDEX ON time (datetime);

-- Fact
CREATE TABLE fact(
 datetime TIMESTAMP WITH TIME ZONE NOT NULL,
 FOREIGN KEY (datetime) REFERENCES time(datetime)
);

-- Function to populate the time dimension
CREATE OR REPLACE FUNCTION decompose_timestamp() RETURNS TRIGGER AS $$
BEGIN
 NEW.datetime = date_trunc('minutes', NEW.datetime);
 INSERT INTO time (datetime, day_of_week)
 VALUES (NEW.datetime, date_part('dow', NEW.datetime));
 RETURN NEW;
EXCEPTION
 WHEN unique_violation THEN
 -- Do nothing if the timestamp already exists in the dimension table.
 RETURN new;
END; $$
LANGUAGE 'plpgsql';

CREATE TRIGGER populate_time BEFORE INSERT
ON fact FOR EACH ROW
EXECUTE PROCEDURE decompose_timestamp();

Regards,
Mark






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

[CS DBA]

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, but the new driver takes 3x longer.


This is on PostgreSQL 9.1

Thoughts?

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] HOT Standby & SSL

[CS DBA]

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?

[CS DBA]

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



You can add a PK constraint with the "USING INDEX" clause:

alter table foo
   add primary key
   using index unique_index_name;









--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] general questions

[CS DBA]

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_tab table 
based on the order of my search_path.
I get that any queries will use the first found table if I don't specify 
the schemaname but
if I'm looking for a full list (i.e. \d with no parameters) I would 
think I should get a full list back


Is this intentional?


2) SET work_mem = x
It seems that any user can run set work_mem = x in a session. While this 
is keen for
folks who know what they are doing, it may not be so keen for folks who 
tend to do
foolish things, especially if a team has several of these types of folks 
on board
i.e. I can Imagine 5 devs all setting work_mem to 5GB each and running 
giant runaway

queries all on a dev server with 8GB of RAM.

Is there a way to restrict this?

3) Can I force unaligned mode AND no wrap for psql output?

4) Is there a way to know for sure ifa sql file was run in single 
transaction mode (after the fact), i.e. something in the logs?


5) Is there a query that will show me the actual prepared SQL text for a 
prepared query?






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[CS DBA]

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"



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] PostgreSQL vs Mongo

[CS DBA]

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 for a 
reporting environment.


what are the use cases where mongo is a good fit?
what are the drawbacks long term?
is mongo a persistent db or simply a big memory cache?
does mongo have advantages over Postgres hstore?
etc...

Thanks in advance...

/Kevin


--
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 switch file systems with least downtime?

[CS DBA]

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 possible


4) shut down the primary db

5) do a final rsync (should have little to nothing to sync)

6) start the secondary db

7) Validate the new db




On 9/14/13 7:46 AM, Moshe Jacobson wrote:
On Sat, Sep 14, 2013 at 9:40 AM, Michael Nolan > wrote:


Have you considered setting up a synchronously replicated slave
database on the new file system (using a port other than 5432),
getting it in sync, then shutting both databases down (master
first), switching the slave over to become the master and
restarting just that database on port 5432?


Not a bad idea, but in this case, a small amount of downtime is 
preferable over going to that level of effort.



Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
mo...@neadwerx.com  | www.neadwerx.com 



"Quality is not an act, it is a habit." -- Aristotle

Re: [GENERAL] Call for design: PostgreSQL mugs

[CS DBA]

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 something new.



What's in for you? Fame, of course - we will announce the designer of 
the next mug along with the mugs itself. Plus 4 mugs for you and your 
friends.



Do you have a cool idea? Please let us know. Either reply here or send 
an email to pgeu-bo...@postgresql.org.



Thanks,


How about a PostgreSQL take on the old "vi reference mug"?

(like this one: 
http://www.cafepress.com/mf/59954381/vi-reference_mugs?utm_medium=cpc&utm_term=577529672&utm_source=google&utm_campaign=sem-cpc-product-ads&utm_content=search-pla)



The new "PostgreSQL Reference Mug" could list several of the most common 
commands & options, and tips & tricks...






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general