Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

OK, just to recap: database A has a table that is 50 GB in size (according to: SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size" from pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; ) I pg_dump -Fc this table, which gives m

Re: [GENERAL] pg_dump makes our system unusable - any way to pg_dump in the middle of the day? (postgres 8.4.4)

Our disk service times and % utilization (according to sar -d) while running pg_dump are low. For example: 01:23:08 AM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 01:23:09 AM sda 1473.00 0.00 98128.00 66.62 0.41 0.28 0.03

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On 27 Feb 2011, at 9:49, Aleksey Tsalolikhin wrote: > Database versions are identical: > A: PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc > (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit > B: PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc > (GCC) 4.1.2 2008070

[GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0xc35c

I am getting error in Postgresql 9.0.1. update import_details_test set data_row = '["4","1 Monor JoÃ\u083ão S. AntÃ\u0083ão (Schools 21,22,76)( Ru)","http://www.asdas.aa.nj.us","","908 436 4861","","","--None--","",","","--None--","","","0","","--None--","0","2008-12-29 17:53:08","","2010-08-2

[GENERAL] PG on two nodes with shared disk ocfs2 & drbd

Hi, I have to build a load balanced pg-cluster and I wanted to ask you, if this configuration would work: A drbd disk in dual primary mode with ocfs2-filesystem. Will there be any conflicts if using the shared volume as PGDATA directory? R+W is a required feature for this cluster. Thank you Ja

[GENERAL] Linking against static libpq using Visual C++

Dear PostgreSQL community, When trying to compile c++ code including libpq against the static version libpq.lib, Visual Studio 2010 gives me the following errors: 1>main.obj : error LNK2019: Unresolved external reference "__imp__exit" in Funktion "_main". 1>libpq.lib(fe-connect.obj) : error L

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On Sun, Feb 27, 2011 at 01:48:24PM +0100, Jasmin Dizdarevic wrote: > A drbd disk in dual primary mode with ocfs2-filesystem. > > Will there be any conflicts if using the shared volume as PGDATA directory? Yes. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (p

Re: [GENERAL] Linking against static libpq using Visual C++

On Sun, Feb 27, 2011 at 15:40, Julia Jacobson wrote: > Dear PostgreSQL community, > > When trying to compile c++ code including libpq against the static version > libpq.lib, Visual Studio 2010 gives me the following errors: > I've built my libpq.lib with Visual Studio and added the include dire

Re: [GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0xc35c

On Feb 27, 2011, at 5:47 AM, AI Rumman wrote: > Any idea please. Don't do that ;-) Seriously, the error means exactly what it says, so you have to figure out why your app is trying to insert invalid UTF-8. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 v

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

Aleksey Tsalolikhin writes: > i've installed the "pageinspect" contrib module as Tomas suggested but > I don't know what to do with it or what to look at. I looked at the > manual for it but it's totally over my head right now. Personally I'd try pgstattuple first: http://www.postgresql.org/docs

Re: [GENERAL] Question about switchover with PG9 replication

Le 07/02/2011 09:57, Wouter D'Haeseleer a écrit : Question 1 : is it possible to have such a replication configuration with the streaming replication of PG9 (cascaded replication) ? Nope, as far as I have tested pg only has 1 master and can have a number of slaves, so having 2 masters i

Re: [GENERAL] Linking against static libpq using Visual C++

Thanks for your answer. I was indeed using the static libpq version from the installer. I tried to compile my own one with Visual Studio 2010, but this caused a lot of problems due to the fact that Visual Studio 2010 has changed the manifest tool. So I built a static version using Visual C++ 200

Re: [GENERAL] Linking against static libpq using Visual C++

On Sun, Feb 27, 2011 at 18:01, Julia Jacobson wrote: > Thanks for your answer. I was indeed using the static libpq version from the > installer. > I tried to compile my own one with Visual Studio 2010, but this caused a lot > of problems due to the fact that Visual Studio 2010 has changed the mani

[GENERAL] Binary params in libpq

Hello, I'm thinking about adding support for PQexecParams and PQprepare in Psycopg. I've posted more details yesterday on the Psycopg mailing list . I have a few preliminary questions: How stable is the binary representation for the Pos

[GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery

I have a hot-standby instance setup using Postgres 9.0.1 with streaming replication against a 9.0.1 master. On the master, I have the following set in the postgresql.conf: checkpoint_segments = 3 checkpoint_timeout = 1min checkpoint_completion_target = 0.5 max_wal_senders = 3 wal_sender_delay = 20

Re: [GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery

On Sonntag, 27. Februar 2011, Sean Laurent wrote: > Unfortunately, most queries against the hot standby fail. Worse > yet, pg_dump fails: ... > I'm not entirely certain I understand why I'm seeing this. Nor do > I understand how to fix or work around this. Any advice or > suggestions would be grea

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On Sunday, February 27, 2011 12:49:48 am Aleksey Tsalolikhin wrote: > so looks like it's something low-level, something about how the data is > stored. > > i've installed the "pageinspect" contrib module as Tomas suggested but > I don't know what to do with it or what to look at. I looked at the

Re: [GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery

On Sun, Feb 27, 2011 at 1:04 PM, Jens Wilke wrote: > On Sonntag, 27. Februar 2011, Sean Laurent wrote: > > > Unfortunately, most queries against the hot standby fail. Worse > > yet, pg_dump fails: > ... > > I'm not entirely certain I understand why I'm seeing this. Nor do > > I understand how to

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On 02/27/11 4:48 AM, Jasmin Dizdarevic wrote: I have to build a load balanced pg-cluster and I wanted... master-master doesn't work real well with databases, especially ones like postgres that are optimized for a high level of concurrency and transactional integrity. on proper hardware, po

Re: [GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery

On Sunday, February 27, 2011 11:57:35 am Sean Laurent wrote: > On Sun, Feb 27, 2011 at 1:04 PM, Jens Wilke wrote: > > On Sonntag, 27. Februar 2011, Sean Laurent wrote: > > > Unfortunately, most queries against the hot standby fail. Worse > > > > > yet, pg_dump fails: > > ... > > > > > I'm not en

[GENERAL] Transactions and ID's generated by triggers

Hi, I have a situation that I dont know how to deal. I have 2 tables "tdir_uris_files" and "tdir_uri_files_details". Please see the Image in attach. The table "tdir_uris_files" have the field "id_ordinal" that is originated by a trigger(before insert) The table "tdir_uri_files_details" use the f

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On Sun, Feb 27, 2011 at 12:10:36PM -0800, John R Pierce wrote: > are made to the master server, but reads are done to either. note you > do NOT want to use block level replication like drbd for this as the > drbd slave can not be actively mounted, nor could the slave instance of > postgres b

Re: [GENERAL] Linking against static libpq using Visual C++

Thanks again for your answer. For all those experiencing similar problems, the missing libraries were: 1) ws2_32.lib 2) secur32.lib (for the SSPI functions) My code is compiled now without errors, but it seems to contain a buffer overflow. So there's still some work left for me ... On Sun, Feb

Re: [GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery

On Sonntag, 27. Februar 2011, Sean Laurent wrote: > In particular, I was really hoping to > run database dumps against the standby, not the master. One solution is to begin idle transactions on the master by using e.g. dblink from the standby to the master before you start pg_dump on the standb

Re: [GENERAL] Linking against static libpq using Visual C++

My application looks like this: #include #include "libpq-fe.h" using namespace std; int main(void) { PGconn *conn; const char *info = "hostaddr = 'postgres.server.com' \ port = '5432' \ dbname = 'mydb' \ user = 'us

Re: [GENERAL] Linking against static libpq using Visual C++

Okay, I could fix it by myself now: One has to tell Visual C++ to ignore msvcrt.lib. A warning message during the compilation of my code already lamented a conflict between msvcrt and the included libraries. I should have taken it for serious instead of just ignoring it. So now everything works

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

Thank you for your detailed information about HA and LB. First of all it's a pitty that there is no built-in feature for LB+HA (both of them, simultaneous). In my eyes, the pgpool2/3-solution has to much disadvantages and restrictions. My idea was the one, that john described: DML and DDL are done

Re: [GENERAL] Transactions and ID's generated by triggers

Using pl/pgsql you can: DECLARE idordinal type; BEGIN INSERT INTO tdir_uris_files RETURNING id_ordinal INTO idordinal; INSERT INTO tdir_uris_files_details (id_ordinal) VALUES (idordinal); END; Similar results are possible in other environments. If you do not have access to "RETURNING"

Re: [GENERAL] Binary params in libpq

On Sun, Feb 27, 2011 at 1:13 PM, Daniele Varrazzo wrote: > Hello, > > I'm thinking about adding support for PQexecParams and PQprepare in > Psycopg. I've posted more details yesterday on the Psycopg mailing > list . I > have a few prelim

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On Mon, Feb 28, 2011 at 12:13:32AM +0100, Jasmin Dizdarevic wrote: > Thank you for your detailed information about HA and LB. First of all it's a > pitty that there is no built-in feature for LB+HA (both of them, > simultaneous). I think it's a pity that I'm not paid a million dollars a year, too,

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On Mon, Feb 28, 2011 at 12:13:32AM +0100, Jasmin Dizdarevic wrote: > My idea was the one, that john described: DML and DDL are done on the small > box and reporting on the "big mama" with streaming replication and hot > stand-by enabled. the only problem is that we use temp tables for reporting > p

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On 02/27/11 4:07 PM, Andrew Sullivan wrote: Multi-master transactional ACID-type databases with multiple masters is very hard. indeed. Oracle RAC works by having a distributed cache and locking manager replicating over a fast bus like infininet. oracle fundamentally uses a transaction re

[GENERAL] ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386

Hi, We have a PG 8.4 database approx 5TB in size. We were recently testing our restore procedure against our latest dump. The dumps are taken using the Continuous Archiving method with base dumps taken using tar. Our tar script is set up to ignore missing/modified files but should stop on all

Re: [GENERAL] Binary params in libpq

On 28/02/2011 7:48 AM, Merlin Moncure wrote: How stable is the binary representation for the PostgreSQL types? We may just pass bytea data in binary format and pass everything else as text parameters, or pass different types too as binary, if performace would benefit. Did binary format ever chan

Re: [GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0xc35c

On 27/02/11 20:47, AI Rumman wrote: > I am getting error in Postgresql 9.0.1. > > update import_details_test > set data_row = '["4","1 Monor JoÃ\u083ão S. AntÃ\u0083ão ^^ Because your email client may have transformed the text encod

Re: [GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery

On 28/02/11 03:57, Sean Laurent wrote: > Right. I read all of that. I guess I just assumed it was possible to create > a snapshot on the standby so that a longer running on the standby could > complete. In particular, I was really hoping to run database dumps against > the standby, not the master.

Re: [GENERAL] PG on two nodes with shared disk ocfs2 & drbd

On Sun, Feb 27, 2011 at 7:17 PM, Andrew Sullivan wrote: > On Mon, Feb 28, 2011 at 12:13:32AM +0100, Jasmin Dizdarevic wrote: >> My idea was the one, that john described: DML and DDL are done on the small >> box and reporting on the "big mama" with streaming replication and hot >> stand-by enabled.

Re: [GENERAL] ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386

On Feb 28, 2011, at 5:55 AM, Michael Harris wrote: > ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386 This seems more like a corrupted toast table. Did you try to reindex the pg_toast_847386? REINDEX table pg_toast.pg_toast_847386; VACUUM ANALYZE ; Thanks & Regard

Re: [GENERAL] Question about switchover with PG9 replication

On Sun, Feb 27, 2011 at 3:46 AM, Cyril Scetbon wrote: > Le 07/02/2011 09:57, Wouter D'Haeseleer a écrit : > > Question 1 : is it possible to have such a replication configuration with > the streaming replication of PG9 (cascaded replication) ? > > Nope, as far as I have tested pg only has 1 master

Re: [GENERAL] ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386

>> ERROR: missing chunk number 0 for toast value 382548694 in >> pg_toast_847386 > > This seems more like a corrupted toast table. > > Did you try to reindex the pg_toast_847386? > REINDEX table pg_toast.pg_toast_847386; > VACUUM ANALYZE ; Hi Vibhor, Thanks for the suggestion. We didn't try th

Re: [GENERAL] ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386

On Feb 28, 2011, at 9:46 AM, Michael Harris wrote: > The main reason we have not gone down that path that after getting this error > we do not have any confidence in the integrity of the rest of the database > after performing the restore - maybe there are many more tables with > corruption in

Re: [GENERAL] ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386

Hi Vibhor, >> Did you find anything suspicious in pg_log? We've been through it all and did not see anything we didn't expect. >> Please share recovery.conf information. We did interrupt the restore a few times. The initial recovery.conf file contained only: restore_command = 'gunzip -c /mnt/

[GENERAL] Win32 8.3.3 install fail (sufficient privileges to install system services)

Dear Yapt, Good day! My name is Jack from DT Research.We are using postgresql to develop our programs; Now we face with a problem when I reinstall the program,it show up: === > Service 'PostgreSQL Database Server 8.3' (pgsql-8.3) > could not be installed. Verify that you have suffi

Re: [GENERAL] ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386

On Feb 28, 2011, at 10:13 AM, Michael Harris wrote: >>> >>> Did you find anything suspicious in pg_log? > > We've been through it all and did not see anything we didn't expect. > >>> Please share recovery.conf information. > > We did interrupt the restore a few times. The initial recovery.con

Re: [GENERAL] ERROR: missing chunk number 0 for toast value 382548694 in pg_toast_847386

Hi Vibhor, >> Not sure about above wrapper function. However, if you can share some >> information from pg_log when you have started the restore with >> backup_label information. Here it is at the beginning: [2011-02-25 09:40:11 EST] LOG: database system was interrupted; last known up at 2011-0