Subject: Re: [GENERAL] wal archiving on a hot-standby server
Mime-Version: 1.0 (Apple Message framework v1084)
Content-Type: multipart/signed; boundary=Apple-Mail-19-50892760; protocol="application/pkcs7-signature"; micalg=sha1
From: Enrico Sirola <enrico.sirola@gmail.com>
In-Reply-To: <CA+U5nM++555RTNadGxSti9x=eEiRyHiX4P4maSA3xiov=bhfDA@mail.gmail.com>
Date: Tue, 22 Nov 2011 10:51:16 +0100
Cc: "PostgreSQL General (list)" <pgsql-general@postgresql.org>
Message-Id: <3A4FB446-8E72-4F05-BCA2-940F3BA980BD@gmail.com>
References: <61F5BA92-5A03-431F-B536-FFF640990E2E@gmail.com> <CA+U5nM++555RTNadGxSti9x=eEiRyHiX4P4maSA3xiov=bhfDA@mail.gmail.com>
To: Simon Riggs <simon@2ndquadrant.com>
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

--Apple-Mail-19-50892760
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Hello Simon,

Il giorno 21/nov/2011, alle ore 15.47, Simon Riggs ha scritto:

> On Mon, Nov 21, 2011 at 10:58 AM, Enrico Sirola =
<enrico.sirola@gmail.com> wrote:
>=20
>> is it possible to archive the WAL files received by a hot-standby =
server? In noticed nothing about this on the pgsql docs. The idea is to =
archive logs in two locations, at the primary site and at the replica =
site (over a wan) in order to be able to perform a PITR also at the =
replica site.
>> Thanks a lot for your help,
>=20
> Not directly, but you can arrange this yourself.
>=20
> Cascading replication is a feature in PG 9.2, released next year.


oh, thanks a lot for the info. By the way, in order to keep an eye on =
the streamed wal activity, I ended up in writing the following functions =
(discovering the correct multiplier was a pain). I'd like a code review =
if someone is available - for what I understood about WALs the functions =
should return the amount of bytes on the WALs since cluster =
initialization. Here's the code:

create or replace function last_xlog_receive_bytes()
returns int8
as
$$
   select cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_receive_location(), '/', =
1),
                                 8, '0')
                    as bit(32))
               as int8) * 16*1024*1024*254 +
          cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_receive_location(), '/', =
2),
                                 8, '0')
                    as bit(32))
               as int8)
$$
language sql immutable strict;


create or replace function last_xlog_replay_bytes()
returns int8
as
$$
   select cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_replay_location(), '/', =
1),
                                 8, '0')
                    as bit(32))
               as int8) * 16*1024*1024*254 +
          cast(cast( 'x' || lpad(split_part(
                                 pg_last_xlog_replay_location(), '/', =
2),
                                 8, '0')
                    as bit(32))
               as int8)
$$
language sql immutable strict;

create or replace function current_xlog_bytes()
returns int8
as
$$
   select cast(cast( 'x' || lpad(split_part(
                                 pg_current_xlog_location(), '/', 1),
                                 8, '0')
                    as bit(32))
               as int8) * 16*1024*1024*254 +
          cast(cast( 'x' || lpad(split_part(
                                 pg_current_xlog_location(), '/', 2),
                                 8, '0')
                    as bit(32))
               as int8)
$$
language sql immutable strict;

Any comment?
Thanks,
e.


--Apple-Mail-19-50892760
Content-Disposition: attachment;
	filename=smime.p7s
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Transfer-Encoding: base64

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIFhzCCBYMw
ggNroAMCAQICAwq7wzANBgkqhkiG9w0BAQUFADB5MRAwDgYDVQQKEwdSb290IENBMR4wHAYDVQQL
ExVodHRwOi8vd3d3LmNhY2VydC5vcmcxIjAgBgNVBAMTGUNBIENlcnQgU2lnbmluZyBBdXRob3Jp
dHkxITAfBgkqhkiG9w0BCQEWEnN1cHBvcnRAY2FjZXJ0Lm9yZzAeFw0xMTA5MjIwOTAzMzJaFw0x
MjAzMjAwOTAzMzJaMGwxGDAWBgNVBAMTD0NBY2VydCBXb1QgVXNlcjEmMCQGCSqGSIb3DQEJARYX
ZW5yaWNvLnNpcm9sYUBnbWFpbC5jb20xKDAmBgkqhkiG9w0BCQEWGWVucmljby5zaXJvbGFAc3Rh
dHByby5jb20wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQC4lZxKhZEmpNGvXd38FyxL
BCNZjRdzZV4MRmWonXF+FO/OYOy8o/mWOeEibQOs9K0UjcYffgvVPG2AbJoIo9DAJ6R7VGYAaNf4
HbvTkTXUCeuf9HW50pT/3AMDkuGGLPCnr6QsdkUQvX/IL+Bt6nR2I2fNttG1s8Cn/RJzGl36HxlX
eY/zrK4Sz//Wl3KKrPFLh52LmUXW28W/zFfqMKDauhMgaTY4I3xUTAbyLKd/nSG+EXr4lo8R6QCb
TWE1NjPhoCaQO+Ln7HuyeOHT7JcLQGH9GhurqleGh36GALb2/iedTQXOvce1VXf6xU7vdzQ6Rcze
SG9gUT132ersXQMPAgMBAAGjggEfMIIBGzAMBgNVHRMBAf8EAjAAMFYGCWCGSAGG+EIBDQRJFkdU
byBnZXQgeW91ciBvd24gY2VydGlmaWNhdGUgZm9yIEZSRUUgaGVhZCBvdmVyIHRvIGh0dHA6Ly93
d3cuQ0FjZXJ0Lm9yZzBABgNVHSUEOTA3BggrBgEFBQcDBAYIKwYBBQUHAwIGCisGAQQBgjcKAwQG
CisGAQQBgjcKAwMGCWCGSAGG+EIEATAyBggrBgEFBQcBAQQmMCQwIgYIKwYBBQUHMAGGFmh0dHA6
Ly9vY3NwLmNhY2VydC5vcmcwPQYDVR0RBDYwNIEXZW5yaWNvLnNpcm9sYUBnbWFpbC5jb22BGWVu
cmljby5zaXJvbGFAc3RhdHByby5jb20wDQYJKoZIhvcNAQEFBQADggIBABuL9q274RyEeZM1iQvt
CBDTzFrGqyDus4UYR1tFbQPtYs7wK0BK+6a9wuS5ewgzMsTNcGweOUWzhCJigxIDv9VBWvck1b8n
/TwCyq/m+nznpiIwJwPQ8tZZolJJ3LjpwayVzDCGNc4IMG24SltebB359TyFGXIdMrBilrS1EN+x
oZNsy8vwhti80cLy7jR/rNtWbOC1UsHxeHtXQeLcUMY674ia2fYLQhxAtFyCi3WOs2+VmmmCitTK
kGs8bY8Z3DySZKfFU0rCMc4GyPAQRYPMOK1YMf04a5iRQP0/50qfxVrk/2zYcq3fNFb/o2ONbpay
QaCRVDDzVS4WVwJN//ky6kcV1ztS4A4Wkdb8rKB8S3uqnckQOo1olAlEtFLosfXwGZwJvkftxZKf
4NM+NHPczZ+lFwxa3u+ZrfTg01QIlSI5d5EaY1cFUSkdKqYmwXZLovDH3aoPHcUqakdCIptEP82y
0GUn3nQEdpEHlrgebpk8YYeKWPeOz5AOvziCTckY/n0b6vo331aU0pHu6M9TrZKFMGm1/ZaQSHnj
DYMP2wlGKtLffWddgsVpKKu52L3bpgsBWwapA/FG7a1sZsIrqfrrXj0bAr6tyojylYVo+jTKuCjO
WO3HILoRrwg0N38V87sPuOyKHJLgvdpmovjEEsFY4ubzitjE4dNW94+jMYIDMzCCAy8CAQEwgYAw
eTEQMA4GA1UEChMHUm9vdCBDQTEeMBwGA1UECxMVaHR0cDovL3d3dy5jYWNlcnQub3JnMSIwIAYD
VQQDExlDQSBDZXJ0IFNpZ25pbmcgQXV0aG9yaXR5MSEwHwYJKoZIhvcNAQkBFhJzdXBwb3J0QGNh
Y2VydC5vcmcCAwq7wzAJBgUrDgMCGgUAoIIBhzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwG
CSqGSIb3DQEJBTEPFw0xMTExMjIwOTUxMTZaMCMGCSqGSIb3DQEJBDEWBBTH1FHSyoKV3r/icj9m
fNYodVZPWTCBkQYJKwYBBAGCNxAEMYGDMIGAMHkxEDAOBgNVBAoTB1Jvb3QgQ0ExHjAcBgNVBAsT
FWh0dHA6Ly93d3cuY2FjZXJ0Lm9yZzEiMCAGA1UEAxMZQ0EgQ2VydCBTaWduaW5nIEF1dGhvcml0
eTEhMB8GCSqGSIb3DQEJARYSc3VwcG9ydEBjYWNlcnQub3JnAgMKu8MwgZMGCyqGSIb3DQEJEAIL
MYGDoIGAMHkxEDAOBgNVBAoTB1Jvb3QgQ0ExHjAcBgNVBAsTFWh0dHA6Ly93d3cuY2FjZXJ0Lm9y
ZzEiMCAGA1UEAxMZQ0EgQ2VydCBTaWduaW5nIEF1dGhvcml0eTEhMB8GCSqGSIb3DQEJARYSc3Vw
cG9ydEBjYWNlcnQub3JnAgMKu8MwDQYJKoZIhvcNAQEBBQAEggEAI8HojnhaN7szaPHmWAZO5QfQ
KHOd9mFje+3lntdCl3b5KxJpSbXz/bk3HEuKc+6OfwCGbuJ8pbprBPIPteN91Fysifcwipx3Yoj+
OO2tKP5vjk/QHQ+ExZRwpewNJ2fIpAn56cLSb5DBJhgde0HLpe8z3HtGlrWxMr6VI/rQbdsptiM4
qPinJoh/A+3G1jb/AjFHFc6fsvy8oRC6lpCuN1Sp3+ohtdSvlYZ8Pbi0PfFa/kOlkqH/ny2etPxh
lZT90FiIpoeMR+6Bzjm/44eIYCkKAklzqp1G2LR2YLmARCfb8rlNcZuBqsolRIX00N4YEXuPZfWl
KTRQJ2QUX96XYwAAAAAAAA==

--Apple-Mail-19-50892760--