[GENERAL] record-based log shipping
Hello, I have a question about log shipping. The documentation from PostgreSQL 8.4, in section 24.4.4, describes how to archive partial WAL files by using the pg_xlogfile_name_offset() function, which it calls record-based log shipping. Although it seems that this section of documentation has been removed in 9.0, the capability is apparently still there. (Of course 9.0 comes with streaming replication, but the ability to perform archiving this way is attractive as it can be used to replicate the database to a non-sql data storage system.) Well, I have successfully written an archiving script to copy the partial WAL segments as described, but I am confused as to how I would actually use this data for a recovery. Section 24.4.4 states that the restore_command scripts still deal in whole WAL files, so the incrementally copied data is not ordinarily made available to the standby servers. It is of use only when the primary dies — then the last partial WAL file is fed to the standby before allowing it to come up. So how would one feed incrementally copied partial WAL file data to a standby (actually a new server used for recovery) as suggested above? Thanks. -- 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] record-based log shipping
On Sun, Aug 21, 2011 at 08:23, Dondi Michael Stroma dstr...@gmail.com wrote: Hello, I have a question about log shipping. The documentation from PostgreSQL 8.4, in section 24.4.4, describes how to archive partial WAL files by using the pg_xlogfile_name_offset() function, which it calls record-based log shipping. Although it seems that this section of documentation has been removed in 9.0, the capability is apparently still there. (Of course 9.0 comes with streaming replication, but the ability to perform archiving this way is attractive as it can be used to replicate the database to a non-sql data storage system.) Well, I have successfully written an archiving script to copy the partial WAL segments as described, but I am confused as to how I would actually use this data for a recovery. Section 24.4.4 states that the restore_command scripts still deal in whole WAL files, so the incrementally copied data is not ordinarily made available to the standby servers. It is of use only when the primary dies — then the last partial WAL file is fed to the standby before allowing it to come up. So how would one feed incrementally copied partial WAL file data to a standby (actually a new server used for recovery) as suggested above? Pad it with zeroes up to the normal segment limit (16Mb), and feed that. PostgreSQL will detect when the correct data ends and the padding begins. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming replication without hot standby
Hi! If I use streaming replication must I setup hot stanby mode on my slave-server? If not then this server would not accept any connections? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-without-hot-standby-tp4719918p4719918.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
[GENERAL] How to apply Postgresql Patch
Hi, I want to learn how to apply patch in Postgresql? Recently, I got the docs http://archives.postgresql.org/pgsql-hackers/2011-08/msg00581.php. I want to apply the patch. Any guideline please?
Re: [GENERAL] Streaming replication without hot standby
On Sun, 2011-08-21 at 00:19 -0700, alexondi wrote: Hi! If I use streaming replication must I setup hot stanby mode on my slave-server? No, you don't have to. If not then this server would not accept any connections? Exactly. Except the streaming one. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] Streaming replication without hot standby
So, how can I check is the slave-server up and properly work (recovery is in progress) in this case? Check the log for some messages? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-without-hot-standby-tp4719918p4720156.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] record-based log shipping
On Sun, Aug 21, 2011 at 7:23 AM, Dondi Michael Stroma dstr...@gmail.com wrote: Hello, I have a question about log shipping. The documentation from PostgreSQL 8.4, in section 24.4.4, describes how to archive partial WAL files by using the pg_xlogfile_name_offset() function, which it calls record-based log shipping. Although it seems that this section of documentation has been removed in 9.0, the capability is apparently still there. (Of course 9.0 comes with streaming replication, but the ability to perform archiving this way is attractive as it can be used to replicate the database to a non-sql data storage system.) Well, I have successfully written an archiving script to copy the partial WAL segments as described, but I am confused as to how I would actually use this data for a recovery. Section 24.4.4 states that the restore_command scripts still deal in whole WAL files, so the incrementally copied data is not ordinarily made available to the standby servers. It is of use only when the primary dies — then the last partial WAL file is fed to the standby before allowing it to come up. So how would one feed incrementally copied partial WAL file data to a standby (actually a new server used for recovery) as suggested above? Using walmgr http://skytools.projects.postgresql.org/doc/walmgr.html -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Streaming replication without hot standby
On Sun, Aug 21, 2011 at 11:12 AM, alexondi alexo...@rambler.ru wrote: So, how can I check is the slave-server up and properly work (recovery is in progress) in this case? Check the log for some messages? It's certainly more tricky... -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Streaming replication without hot standby
But still, how to do it (check server state) in this case? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-without-hot-standby-tp4719918p4720634.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
[GENERAL] record-based log shipping
Hello, I have a question about log shipping. The documentation from PostgreSQL 8.4, in section 24.4.4, describes how to archive partial WAL files by using the pg_xlogfile_name_offset() function, which it calls record-based log shipping. Although it seems that this section of documentation has been removed in 9.0, the capability is apparently still there. (Of course 9.0 comes with streaming replication, but the ability to perform archiving this way is attractive as it can be used to replicate the database to a non-sql data storage system.) Well, I have successfully written an archiving script to copy the partial WAL segments as described, but I am confused as to how I would actually use this data for a recovery. Section 24.4.4 states that the restore_command scripts still deal in whole WAL files, so the incrementally copied data is not ordinarily made available to the standby servers. It is of use only when the primary dies — then the last partial WAL file is fed to the standby before allowing it to come up. So how would one feed incrementally copied partial WAL file data to a standby (actually a new server used for recovery) as suggested above? Thanks. -- 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 apply Postgresql Patch
On Sun, Aug 21, 2011 at 2:05 AM, AI Rumman rumman...@gmail.com wrote: Hi, I want to learn how to apply patch in Postgresql? Recently, I got the docs http://archives.postgresql.org/pgsql-hackers/2011-08/msg00581.php. I want to apply the patch. Any guideline please? Applying the patch requires that you have the source tree downloaded and can build from there. If you've installed from a package, then you'll need to run the pg_config command that comes with your package. It's often part of the -dev or -devel package. That command will tell you all the switches used to build your package. It's often much better to test this patch on a non-production machine, so go to another machine and get the latest postgresql package that matches your major version. I.e. if you're running 8.3.5 in production you'll want to download 8.3.15. Untar it and then cd into the src directory and apply the patch something like: patch -p0 patchfile and see if it applies. If it complains about the file not being in the right dir or something hollar back and we'll see about getting you patched up. After that, in the main postgresql directory run configure ./configure --switchsettingsfrompg_config_go_here make sudo make install Note that if this is a bit too confusing, you might wanna find someone more familiar with unix and patching and what not, as it's not a simple obvious process for first time users. -- 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] Streaming replication without hot standby
On Sun, 2011-08-21 at 08:04 -0700, alexondi wrote: But still, how to do it (check server state) in this case? pg_controldata is probably a good solution. Or checking process titles with ps -ef. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] record-based log shipping
On Sun, Aug 21, 2011 at 3:14 AM, Magnus Hagander mag...@hagander.net wrote: On Sun, Aug 21, 2011 at 08:23, Dondi Michael Stroma dstr...@gmail.com wrote: So how would one feed incrementally copied partial WAL file data to a standby Pad it with zeroes up to the normal segment limit (16Mb), and feed that. PostgreSQL will detect when the correct data ends and the padding begins. Thanks, that is what I was looking for. Worked perfectly. -- 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] idle in transaction process
Can anyone please suggest me on this . Thanks.. Tamanna On Thu, Aug 18, 2011 at 2:47 PM, tamanna madaan tamanna.mad...@globallogic.com wrote: Hi Yes , restarting the slon resolves the issue. But. there are other processes also in my application which connect to postgres . Those processes can also cause idle in transaction postgres connection . So, I was wondering if I can make use of tcp_keepalives_idle , tcp_keepalives_interval and tcp_keepalives_count configurations in postgresql.conf to get rid of this idle in transaction process after a certain amount of time . Will this help or not ?? Thanks... Tamanna On Tue, Aug 16, 2011 at 11:56 AM, Abbas abbas@gmail.com wrote: Best Regards, Abbas On Mon, Aug 15, 2011 at 11:14 PM, tamanna madaan tamanna.mad...@globallogic.com wrote: Hi All I am using postgres-8.4.0 on a cluster setup with slony-2.0.4 being used for replication. Recently , I saw a idle in transaction postgres process as below. postgres 13052 14742 0 May13 ? 00:00:00 postgres: slon abc 172.16.1.1(49017) idle in transaction I wonder what could have lead to that hung postgres process . I googled about it a lot and they say that it could be because of abrupt netwotk issue between slony and postgres . But in my case slon was connected to its local postgres database. So, network wont be an issue in this case . What else could be the reason for this hung process ? What should I do to come over this kind of issue in future. I think this hung process would have taken locks on various tables. I wonder if killing the idle in transaction process would cause the locks on the tables to be released or not. Can anyone please help me on that. Of course it is a slon process if it is not due to a network issue, then might be any of your scripts, if not you can try by restarting the slon process on origin. Abbas. Thanks in Advance . Tamanna -- Tamanna Madaan | Associate Consultant | GlobalLogic Inc. Leaders in Software RD Services ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA Office: +0-120-406-2000 x 2971 www.globallogic.com -- Tamanna Madaan | Associate Consultant | GlobalLogic Inc. Leaders in Software RD Services ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA Office: +0-120-406-2000 x 2971 www.globallogic.com
Re: [GENERAL] idle in transaction process
On 08/18/11 2:17 AM, tamanna madaan wrote: Yes , restarting the slon resolves the issue. But. there are other processes also in my application which connect to postgres . Those processes can also cause idle in transaction postgres connection . So, I was wondering if I can make use of tcp_keepalives_idle , tcp_keepalives_interval and tcp_keepalives_count configurations in postgresql.conf to get rid of this idle in transaction process after a certain amount of time . Will this help or not ?? Idle In Transaction means that the process has issued a BEGIN to start a transaction but is sitting there doing nothing. if they stay that way for hours, they block vacuum from freeing any tuples since the start of the oldest transaction.. Long running Idle in Transaction processes generally indicate a error in your program design. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] idle in transaction process
On Sun, Aug 21, 2011 at 10:52 PM, tamanna madaan tamanna.mad...@globallogic.com wrote: Can anyone please suggest me on this . Both the PostgreSQL and Slony versions you are running have known bugs that cause problems. Update them first and see if your problem goes away. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general