[BUGS] BUG #6094: Streaming replication does not catch up when writing enough data
The following bug has been logged online:
Bug reference: 6094
Logged by: David Hartveld
Email address: david.hartv...@mendix.com
PostgreSQL version: 9.1-beta2
Operating system: Debian GNU/Linux 6.0.2 "Squeeze"
Description:Streaming replication does not catch up when writing
enough data
Details:
After creation of two new clusters, and setting them up as master and slave
(in async mode, according to the current 9.1 docs), the execution of a large
SQL script (creating a db, tables, sequences, etc., filling them with data
through COPY) runs properly on the master, but does not stream to the slave,
i.e. the slave does not catch up. In the master log, the following line is
printed many times:
2011-07-07 13:48:27 CEST LOG: could not send data to client: Connection
reset by peer
In the slave log, the following corresponding lines are printed, for each
log line on the master:
2011-07-07 13:48:27 CEST LOG: streaming replication successfully connected
to primary
2011-07-07 13:48:27 CEST LOG: record with zero length at 0/51E0010
2011-07-07 13:48:27 CEST FATAL: terminating walreceiver process due to
administrator command
cp: cannot stat `/walshipping/9.1/test/00010005': No such
file or directory
2011-07-07 13:48:27 CEST LOG: record with zero length at 0/51E4010
cp: cannot stat `/walshipping/9.1/test/00010005': No such
file or directory
The 'record with zero length' line is printed many times.
I have configured the clusters with the following 'script':
EDITOR=/usr/bin/vim
MASTER=pg-db-01
SLAVE=pg-db-02
PORT=3000
VERSION=9.1
CLUSTERNAME=test
BOTH
- Create 9.1 cluster on port 3000
# pg_createcluster -p $PORT $VERSION $CLUSTERNAME
- Add line 'host all all samenet trust' to pg_hba.conf.
# $EDITOR /etc/postgresql/$VERSION/$CLUSTERNAME/pg_hba.conf
- Listen on all IPs: Change 'listen_addresses' to '*' in postgresql.conf.
# $EDITOR /etc/postgresql/$VERSION/$CLUSTERNAME/postgresql.conf
MASTER
- Enable wal archiving. Set the following configuration parameters in
postgresql.conf
(and create directory /walshipping/9.1/test, owned by postgres):
wal_level = hot_standby
archive_mode = on
archive_command = 'cp -i %p /walshipping/9.1/test/%f < /dev/null'
# $EDITOR /etc/postgresql/$VERSION/$CLUSTERNAME/postgresql.conf
- To enable streaming replication, set the following configuration
parameters in postgresql.conf:
wal_keep_segments = 64 # * 16 MiB, 1 GiB disk space needed.
max_wal_senders = 1 # Or some other number at least equal to the number
of standby servers.
# $EDITOR /etc/postgresql/$VERSION/$CLUSTERNAME/postgresql.conf
- Also add line 'host replication postgres samenet trust' to pg_hba.conf
# $EDITOR /etc/postgresql/$VERSION/$CLUSTERNAME/pg_hba.conf
- Start the cluster.
# pg_ctlcluster $VERSION $CLUSTERNAME start
- Create a base backup for the slave.
# psql -U postgres -h localhost -p $PORT \
-c "SELECT pg_start_backup('base', true)"
# rsync -a /var/lib/postgresql/$VERSION/$CLUSTERNAME/*
/pgbackup/$VERSION/$CLUSTERNAME/
# psql -U postgres -h localhost -p $PORT \
-c "SELECT pg_stop_backup()"
# rm -rf /pgbackup/$VERSION/$CLUSTERNAME/{postmaster.pid,pg_xlog/*}
# cd /pgbackup/$VERSION
# tar jcvf $CLUSTERNAME.tar.bz2 ./$CLUSTERNAME/
SLAVE
- 'Restore' the created backup from the master.
# cd /var/lib/postgresql/$VERSION
# rm -rf $CLUSTERNAME.orig
# mv -f $CLUSTERNAME $CLUSTERNAME.orig
# tar jxvf /$CLUSTERNAME.tar.bz2
- Create recovery.conf with the following configuration parameters:
standby_mode = 'on'
primary_conninfo = 'host=$MASTER port=$PORT user=postgres'
restore_command = 'cp /walshipping/$VERSION/$CLUSTERNAME/%f %p'
# $EDITOR /var/lib/postgresql/$VERSION/$CLUSTERNAME/recovery.conf
- Start the cluster.
# chown -R postgres.postgres $CLUSTERNAME
# chmod 0700 $CLUSTERNAME
# pg_ctlcluster $VERSION $CLUSTERNAME start
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6094: Streaming replication does not catch up when writing enough data
On Thu, Jul 7, 2011 at 1:05 PM, David Hartveld wrote: > > The following bug has been logged online: > > Bug reference: 6094 > Logged by: David Hartveld > Email address: david.hartv...@mendix.com > PostgreSQL version: 9.1-beta2 > Operating system: Debian GNU/Linux 6.0.2 "Squeeze" > Description: Streaming replication does not catch up when writing > enough data > Details: > > After creation of two new clusters, and setting them up as master and slave > (in async mode, according to the current 9.1 docs), the execution of a large > SQL script (creating a db, tables, sequences, etc., filling them with data > through COPY) runs properly on the master, but does not stream to the slave, > i.e. the slave does not catch up. In the master log, the following line is > printed many times: Your output indicates that there is a problem in your replication setup and this is why the slave does not catch up. This is not a performance issue. It is either a bug in replication, or a user configuration issue. Since few things have changed in 9.1 in this area, at the moment the balance of probablity if user error. If you can provide a more isolated bug report we may be able to investigate. This is being discussed in a thread on the General list and there is no reason to post twice. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] PostgreSQL fails to build with 32bit MinGW-w64
Hi, couple of weeks (maybe months) ago postgresql introduced support for mingw-w64 compiler on MS Windows platform; however the related patch was based on assumption that mingw-w64 project delivers just 64bit compiler (which is not true - they deliver both 32/64bit compilers). According to my testing - postgresql-9.0.4 does compile fine with 64bit mingw-w64 but fails with 32-bit mingw-w64 compiler. Try to have a look at the enclosed patch which makes it possible to compile postgresql-9.0.4 with 32bit mingw-w64. -- kmx diff -r -u -w --strip-trailing-cr /z/strawberry_libs/build/_wrk_libs2011_/postgresql-9.0.4/src/include/c.h /z/strawberry_libs/build/../patches/postgresql-9.0.4/src/include/c.h --- /z/strawberry_libs/build/_wrk_libs2011_/postgresql-9.0.4/src/include/c.h 2011-04-15 03:15:53 + +++ /z/strawberry_libs/build/../patches/postgresql-9.0.4/src/include/c.h 2011-05-06 11:39:13 + @@ -58,17 +58,21 @@ #endif #include "postgres_ext.h" -#if _MSC_VER >= 1400 || defined(WIN64) -#define errcode __msvc_errcode -#include -#undef errcode -#endif - #include #include #include #include #include + +/* note: __MINGW64_VERSION_MAJOR is related to both 32/bit gcc compiles by mingw-w64 + * however it gots defined only after #include + */ +#if _MSC_VER >= 1400 || defined(WIN64) || defined(__MINGW64_VERSION_MAJOR) +#define errcode __msvc_errcode +#include +#undef errcode +#endif + #ifdef HAVE_STRINGS_H #include #endif diff -r -u -w --strip-trailing-cr /z/strawberry_libs/build/_wrk_libs2011_/postgresql-9.0.4/src/include/port/win32.h /z/strawberry_libs/build/../patches/postgresql-9.0.4/src/include/port/win32.h --- /z/strawberry_libs/build/_wrk_libs2011_/postgresql-9.0.4/src/include/port/win32.h 2011-04-15 03:15:53 + +++ /z/strawberry_libs/build/../patches/postgresql-9.0.4/src/include/port/win32.h 2011-05-06 11:46:54 + @@ -31,7 +31,7 @@ * The Mingw64 headers choke if this is already defined - they * define it themselves. */ -#if !defined(WIN64) || defined(WIN32_ONLY_COMPILER) +#if (!defined(WIN64) || defined(WIN32_ONLY_COMPILER)) && !defined(__MINGW64_VERSION_MAJOR) #define _WINSOCKAPI_ #endif #include diff -r -u -w --strip-trailing-cr /z/strawberry_libs/build/_wrk_libs2011_/postgresql-9.0.4/src/port/getaddrinfo.c /z/strawberry_libs/build/../patches/postgresql-9.0.4/src/port/getaddrinfo.c --- /z/strawberry_libs/build/_wrk_libs2011_/postgresql-9.0.4/src/port/getaddrinfo.c 2011-04-15 03:15:53 + +++ /z/strawberry_libs/build/../patches/postgresql-9.0.4/src/port/getaddrinfo.c 2011-05-06 11:20:18 + @@ -329,7 +329,7 @@ return "Not enough memory"; #endif #ifdef EAI_NODATA -#if !defined(WIN64) && !defined(WIN32_ONLY_COMPILER) /* MSVC/WIN64 duplicate */ +#if !defined(WIN64) && !defined(WIN32_ONLY_COMPILER) && !defined(__MINGW64_VERSION_MAJOR) /* MSVC/WIN64 duplicate */ case EAI_NODATA: return "No host data of that type was found"; #endif -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6095: Misleading error message: INSERT has more expressions than target columns
The following bug has been logged online: Bug reference: 6095 Logged by: Gavin Flower Email address: gavin.flo...@archidevsys.co.nz PostgreSQL version: 9.1beta2 Operating system: x86_64 Linux Description:Misleading error message: INSERT has more expressions than target columns Details: Using pg 9.1beta2 compiled using gcc (GCC) 4.5.1 20100924 (Red Hat 4.5.1-4), on a 64 bit Linux system. In the 'INSERT INTO' construct: I think that psql should first check that the fields listed after the table name are valid, before checking that the number of columns specified is sufficient for the number of expressions. Due to my poor eyesight: it took me a few minutes before I realized that I had used a fullstop '.' rather than a comma ',' in the list of columns. There is neither a schema, nor a database, called 'id' - so I would have thought that pg should have been able to detect the error. DROP TABLE wage; CREATE TABLE wage ( id int PRIMARY KEY, pay numeric(5,2) ); INSERT INTO wage (id. pay) VALUES (1, 4.2); TABLE wage; output $ psql < bug.sql DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "wage_pkey" for table "wage" CREATE TABLE ERROR: INSERT has more expressions than target columns LINE 2: VALUES (1, 4.2); ^ id | pay +- (0 rows) $ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6095: Misleading error message: INSERT has more expressions than target columns
"Gavin Flower" writes: > In the 'INSERT INTO' construct: I think that psql should first check that > the fields listed after the table name are valid, before checking that the > number of columns specified is sufficient for the number of expressions. Hmm ... I think this would just move the set of unpleasant cases around. Usually I find it's better if we check the count first. For example, if you accidentally stick an extra value into the VALUES list, and we weren't checking the count first, you'd likely get weird invalid-input-value complaints that stem from matching subsequent input items to the wrong column. In such cases it saves a lot of time if we first point out the wrong number of values. > Due to my poor eyesight: it took me a few minutes before I realized that I > had used a fullstop '.' rather than a comma ',' in the list of columns. > There is neither a schema, nor a database, called 'id' - so I would have > thought that pg should have been able to detect the error. Actually, what you get if you change the number of VALUES columns is: ERROR: cannot assign to field "pay" of column "id" because its type integer is not a composite type LINE 1: INSERT INTO wage (id. pay) ^ This *is* valid syntax, if id is a composite-type column. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6096: ECPG set precision of double value to 14 digits
The following bug has been logged online:
Bug reference: 6096
Logged by: Akira Kurosawa
Email address: kurosawa-ak...@mxc.nes.nec.co.jp
PostgreSQL version: 8.4.5
Operating system: Red Hat Enterprise Linux 5.5
Description:ECPG set precision of double value to 14 digits
Details:
When I used host variable of double type in ECPG code,
the precision of the data was treated as 14 digits.
I executed following command.
--
EXEC SQL BEGIN DECLARE SECTION;
double dbl;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO testdb@localhost:5432 USER postgres;
dbl = 12345678901234; -- 14 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 1234567890.1234; -- 14 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 0.12345678901234; -- 14 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 123456789012345; -- 15 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 1234567890.12345; -- 15 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
dbl = 0.123456789012345; -- 15 digits
EXEC SQL INSERT INTO double_table (c1) VALUES (:dbl);
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
--
Then I checked inserted data, but all data was stored by 14 digits
precision.
--
testdb=# \d double_table
Table "public.double_table"
Column | Type | Modifiers
+--+---
c1 | double precision |
testdb=# SELECT * FROM double_table;
c1
--
12345678901234
1234567890.1234
0.12345678901234
123456789012340
1234567890.1235
0.12345678901234
(6 rows)
--
The precision of double type should be 15 digits,
and I think this result is wrong.
PostgreSQL 9.0.4 also shows the same result.
In function "ecpg_store_input:execute.c",
the value of double type is changed by sprintf to a character string
at 14 digits precision. I'm thinking this point is a problem.
sprintf(mallocedval, "%.14g", *((double *) var->value));
Following log is result of executing same query by psql.
--
testdb=# INSERT INTO double_table VALUES ('12345678901234');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('1234567890.1234');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('0.12345678901234');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('123456789012345');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('1234567890.12345');
INSERT 0 1
testdb=# INSERT INTO double_table VALUES ('0.123456789012345');
INSERT 0 1
testdb=# SELECT * FROM double_table;
c1
---
12345678901234
1234567890.1234
0.12345678901234
123456789012345
1234567890.12345
0.123456789012345
(6 rows)
--
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6097: Server crash when enabling custom_variable_classes
The following bug has been logged online: Bug reference: 6097 Logged by: Maxim Boguk Email address: maxim.bo...@gmail.com PostgreSQL version: 9.0 9.1beta Operating system: Linux/Freebsd Description:Server crash when enabling custom_variable_classes Details: I found crash bug in custom_variable_classes work. Test case simple: start server with empty custom_variable_classes Then add to config file: custom_variable_classes = 'something'# list of custom variable class names something.location = 'ru' And then perform select pg_reload_conf(); Ooops... you have no more working server (but client connections staying alive). Same crash with 9.0 and 9.1beta. Adding just custom_variable_classes = '' and pg_reload_conf() do not crash server. But after I added .location = 'ru' to config and perform second pg_reload_conf() server crashed again. The same happens if server started with just custom_variable_classes = '', after I added .location = 'ru' and performend pg_reload_conf() server crashed. However with both option enabled server start and work without any issues. PS: it is exeptionally strange no one happen to be hit by that problem before. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
