Re: [GENERAL] Backups over slave instead master?
On Thu, May 15, 2014 at 1:55 PM, Bruce Momjian wrote: > On Thu, May 1, 2014 at 12:39:44PM -0700, bricklen wrote: > > Or alternatively, if "backup" = pg_dump, then backups can taken from the > slave > > too. Have a look at pg_xlog_replay_pause() + pg_dump + > pg_xlog_replay_resume(). > > http://www.postgresql.org/docs/current/static/functions-admin.html# > > FUNCTIONS-RECOVERY-CONTROL-TABLE > > Uh, what is the pause for? So the transaction will not be cancelled? > > Yes.
[GENERAL] Somebody hijacked @psql ?
Looks like someone hijacked the tweeter account @psql, and is posting what looks like spam on http://forum.postgresql.org.pl, and then spaming those posts with the tweeter account. I just wanted to let the person in charge know. Cheers, -- Martín Marqués 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] Backups over slave instead master?
On Thu, May 1, 2014 at 12:39:44PM -0700, bricklen wrote: > > On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas wrote: > > On 05/01/2014 10:31 AM, Edson Richter wrote: > > > I'm wondering if would be possible to execute these backups in the > slave > server instead, so I can avoid the overhead of backups on master > system? > > > If you're on PostgreSQL 9.3, you can backup the slave server safely. If > not, you'll need to run this command on the master system first: > > SELECT pg_start_backup('some-label'); > > After the backup is done, run this on the master server: > > SELECT pg_stop_backup(); > > > Or alternatively, if "backup" = pg_dump, then backups can taken from the slave > too. Have a look at pg_xlog_replay_pause() + pg_dump + > pg_xlog_replay_resume(). > http://www.postgresql.org/docs/current/static/functions-admin.html# > FUNCTIONS-RECOVERY-CONTROL-TABLE Uh, what is the pause for? So the transaction will not be cancelled? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] are analyze statistics synced with replication?
On Thu, May 15, 2014 at 6:39 AM, Dorian Hoxha wrote: > If you don't do read queries on the slave than it will not have hot > data/pages/rows/tables/indexes in ram like the primary ? > Yeah, that was the first thing we noticed, the cacti graph shows it took two hours for the page cache to fill up our 64GB of RAM, but I/O didn't stop sucking after that.
Re: [GENERAL] Re: TODO: Expose parser support for decoding unicode escape literals to user
On 05/15/2014 07:13 AM, David G Johnston wrote: Adrian Klaver-4 wrote On 05/15/2014 01:31 AM, Craig Ringer wrote: Hi all I just noticed a Stack Overflow question (http://stackoverflow.com/q/20124393/398670) where someone's asking how to decode '\u` style escapes *stored in database text fields* into properly encoded text strings. The parser supports this for escape-strings, and you can write E'\u011B' to get 'ě' because of http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html. I don't see this exposed in a way that users can call directly, though. 'decode(bytea, text)' has the 'escape' input, but it expects octal. It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the work, but that's downright awful. Am I missing something obvious, or is this something that'd be a good new-developer TODO? Not sure if this is what you want?: test=> SELECT quote_literal(E'test \u011B'); quote_literal --- 'test ě' Except the data is already in the database and there is no way to put an "E" in front of a column name and cause PostgreSQL to process the escapes embedded in the column's value in the same way it processes a literal. Yea, that is a problem. WITH src (txt) AS ( VALUES ('A \u011B C') ) SELECT txt FROM src; Hence the need for a function to perform the same process that the parser performs when dealing with literals. David J. -- Adrian Klaver adrian.kla...@aklaver.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] Re: TODO: Expose parser support for decoding unicode escape literals to user
Adrian Klaver-4 wrote > On 05/15/2014 01:31 AM, Craig Ringer wrote: >> Hi all >> >> I just noticed a Stack Overflow question >> (http://stackoverflow.com/q/20124393/398670) where someone's asking how >> to decode '\u` style escapes *stored in database text fields* into >> properly encoded text strings. >> >> The parser supports this for escape-strings, and you can write E'\u011B' >> to get 'ě' because of >> http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html. >> >> I don't see this exposed in a way that users can call directly, though. >> 'decode(bytea, text)' has the 'escape' input, but it expects octal. >> >> It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the >> work, but that's downright awful. >> >> Am I missing something obvious, or is this something that'd be a good >> new-developer TODO? >> > > Not sure if this is what you want?: > > test=> SELECT quote_literal(E'test \u011B'); > quote_literal > --- > 'test ě' Except the data is already in the database and there is no way to put an "E" in front of a column name and cause PostgreSQL to process the escapes embedded in the column's value in the same way it processes a literal. WITH src (txt) AS ( VALUES ('A \u011B C') ) SELECT txt FROM src; Hence the need for a function to perform the same process that the parser performs when dealing with literals. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/TODO-Expose-parser-support-for-decoding-unicode-escape-literals-to-user-tp5804012p5804042.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] TODO: Expose parser support for decoding unicode escape literals to user
On 05/15/2014 01:31 AM, Craig Ringer wrote: Hi all I just noticed a Stack Overflow question (http://stackoverflow.com/q/20124393/398670) where someone's asking how to decode '\u` style escapes *stored in database text fields* into properly encoded text strings. The parser supports this for escape-strings, and you can write E'\u011B' to get 'ě' because of http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html. I don't see this exposed in a way that users can call directly, though. 'decode(bytea, text)' has the 'escape' input, but it expects octal. It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the work, but that's downright awful. Am I missing something obvious, or is this something that'd be a good new-developer TODO? Not sure if this is what you want?: test=> SELECT quote_literal(E'test \u011B'); quote_literal --- 'test ě' -- Adrian Klaver adrian.kla...@aklaver.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] are analyze statistics synced with replication?
If you don't do read queries on the slave than it will not have hot data/pages/rows/tables/indexes in ram like the primary ? (it smoked weed and was happy doing nothing so it was happy, but when responsibility came (being promoted to master) it failed hard) On Thu, May 15, 2014 at 6:46 AM, Kevin Goess wrote: > We have a master/slave setup with replication. Today we failed over to > the slave and saw disk I/O go through the roof. > > Are the pg_statistic statistics synced along with streaming replication? > Are you expected to have to do a vacuum analyze after failing over? That's > what we're trying now to see if it makes a difference. Our next step will > be to fall back to the first host and see where this one went wrong > (society? lax discipline at home? the wrong sort of friends?) > > >
Re: [GENERAL] Correct update statement
Thank very much *From:* pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] *On Behalf Of *Sim Zacks *Sent:* Thursday, May 15, 2014 10:42 AM *To:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] Correct update statement update contacts set addr_id=b.addr_id from (select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null) b where contacts.con_id=b.con_id On 05/15/2014 09:19 AM, Khangelani Gama wrote: Hi Please help, we are using postgreSQL 9.2.4. I need to update over 9000 rows. See the query below: A table called contact has got *addr_id *field as null which is incorrect. So now I need to update contact table for each account (cus_acno is in cus table) where contact_addr_id is null. For example using the first of the results below: I need take set addr_id (in contact table) to 187479 where cus_acno = 243492 and con_id = 119360 Example: select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null; cus_acno | con_id | addr_id --++- 243492 | 119360 | 187479 393701 | 119824 | 458532 388538 | 118413 | 453178 Thanks CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] Correct update statement
update contacts set addr_id=b.addr_id from (select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null) b where contacts.con_id=b.con_id On 05/15/2014 09:19 AM, Khangelani Gama wrote: Hi Please help, we are using postgreSQL 9.2.4. I need to update over 9000 rows. See the query below: A table called contact has got addr_id field as null which is incorrect. So now I need to update contact table for each account (cus_acno is in cus table) where contact_addr_id is null. For example using the first of the results below: I need take set addr_id (in contact table) to 187479 where cus_acno = 243492 and con_id = 119360 Example: select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null; cus_acno | con_id | addr_id --++- 243492 | 119360 | 187479 393701 | 119824 | 458532 388538 | 118413 | 453178 Thanks CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
[GENERAL] TODO: Expose parser support for decoding unicode escape literals to user
Hi all I just noticed a Stack Overflow question (http://stackoverflow.com/q/20124393/398670) where someone's asking how to decode '\u` style escapes *stored in database text fields* into properly encoded text strings. The parser supports this for escape-strings, and you can write E'\u011B' to get 'ě' because of http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html. I don't see this exposed in a way that users can call directly, though. 'decode(bytea, text)' has the 'escape' input, but it expects octal. It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the work, but that's downright awful. Am I missing something obvious, or is this something that'd be a good new-developer TODO? -- Craig Ringer 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