[GENERAL] pg_basebackup fails on inactive slave

I have PostgreSQL 9.2.6 (pgdg-92-centos.repo) on CentOS 6.4 amd64. I have a problem when I do pg_basebackup on slave with following options pg_basebackup -w -U replication -h 127.0.0.1 -p 5432\ -D /mnt/basebackups_last/ -F p -l "backup_24.02.2015" -x I got message: pg_basebackup: could no

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-23 14:14 GMT-05:00 Adrian Klaver : > On 02/23/2015 10:08 AM, Guillaume Drolet wrote: > >> Hello, >> >> I moved all my tables and indexes from one tablespace to pg_default using >> >> ALTER TABLE ... SET TABLESPACE pg_default; >> ALTER INDEX ... SET TABLESPACE pg_default; >> >> Some 2500 fi

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 7:07 GMT-05:00 Guillaume Drolet : > > > 2015-02-23 14:14 GMT-05:00 Adrian Klaver : > > On 02/23/2015 10:08 AM, Guillaume Drolet wrote: >> >>> Hello, >>> >>> I moved all my tables and indexes from one tablespace to pg_default using >>> >>> ALTER TABLE ... SET TABLESPACE pg_default; >>> A

Re: [GENERAL] Leap second impact on postgreSQL on June 30 2015

On Tue, Feb 24, 2015 at 2:15 AM, Mitu Verma wrote: > What could be the possible impacts of leap second on June 30 2105 (which > will make the one second longer time) at PostgreSQL database ? > > As an experiment, try setting the time to the leap second and see if postgres stores it as you expect.

Re: [GENERAL] Sequences not moved to new tablespace

Guillaume Drolet wrote: > Digging a little more, I found that not only sequences were not moved but > also many tables in > pg_catalog are still in my old tablespace. This is expected since the query > in the SQL files I used to > move the tables and indexes had a WHERE clause like this: > > SEL

Re: [GENERAL] SQL solution for my JDBC timezone issue

-- In your original post you mentioned that access to the databases is through a Web server. -- Is there just one Web server with one time zone? We have 2 web servers that are clustered together. They are both set to Eastern since that is the timezone they are located in. iGLASS Networks www.

Re: [GENERAL] SQL solution for my JDBC timezone issue

George, One solution for you might be to write a C function which gets the OS timezone and then you can execute set timezone=server_timezone(); Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On 24 February 2015 at 09:25, George Woodring wrote: > -- In your original post

Re: [GENERAL] Sequences not moved to new tablespace

On 02/24/2015 05:06 AM, Guillaume Drolet wrote: 2015-02-24 7:07 GMT-05:00 Guillaume Drolet mailto:droletguilla...@gmail.com>>: 2015-02-23 14:14 GMT-05:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>: On 02/23/2015 10:08 AM, Guillaume Drolet wrote: Hello,

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 8:45 GMT-05:00 Albe Laurenz : > Guillaume Drolet wrote: > > Digging a little more, I found that not only sequences were not moved > but also many tables in > > pg_catalog are still in my old tablespace. This is expected since the > query in the SQL files I used to > > move the tables an

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:06 GMT-05:00 Adrian Klaver : > On 02/24/2015 05:06 AM, Guillaume Drolet wrote: > >> >> >> 2015-02-24 7:07 GMT-05:00 Guillaume Drolet > >: >> >> >> >> 2015-02-23 14:14 GMT-05:00 Adrian Klaver > >: >> >> >>

Re: [GENERAL] Sequences not moved to new tablespace

On 02/24/2015 07:10 AM, Guillaume Drolet wrote: So how did all this stuff get into the non-default tablespace in the first place? Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files mentionned in my previous post. The answer would seem to be just reverse whate

Re: [GENERAL] SQL solution for my JDBC timezone issue

On 02/24/2015 06:25 AM, George Woodring wrote: -- In your original post you mentioned that access to the databases is through a Web server. -- Is there just one Web server with one time zone? We have 2 web servers that are clustered together. They are both set to Eastern since that is the

Re: [GENERAL] Sequences not moved to new tablespace

Adrian Klaver writes: > On 02/24/2015 07:10 AM, Guillaume Drolet wrote: >> That makes sense. I will give it a try. Thanks. > Alright, now I am thoroughly confused:) I thought this is how you to > this point, using the above commands to move from the non-default > tablespace back to the default

Re: [GENERAL] SQL solution for my JDBC timezone issue

> > > > So: > > JDBC Web servers(US/East) <---> 90 database (5 different timezones) > > Therefore everything to the end user is passed through the Web servers? > > Is there a reason why the databases have different timezones? > > Seems to me less complicated to have all the databases share the

Re: [GENERAL] Sequences not moved to new tablespace

On 02/24/2015 07:32 AM, Tom Lane wrote: Adrian Klaver writes: On 02/24/2015 07:10 AM, Guillaume Drolet wrote: That makes sense. I will give it a try. Thanks. Alright, now I am thoroughly confused:) I thought this is how you to this point, using the above commands to move from the non-defaul

Re: [GENERAL] Sequences not moved to new tablespace

Guillaume Drolet wrote: >> If you want to move a whole database to a different tablespace (the only >> reason >> I can think of for doing what you are trying to so), use the command >> ALTER DATABASE ... SET TABLESPACE ... > Thanks Laurenz. I tried your suggestion: > > psql -U postgres -c "ALTER

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:32 GMT-05:00 Tom Lane : > Adrian Klaver writes: > > On 02/24/2015 07:10 AM, Guillaume Drolet wrote: > >> That makes sense. I will give it a try. Thanks. > > > Alright, now I am thoroughly confused:) I thought this is how you to > > this point, using the above commands to move from t

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:18 GMT-05:00 Adrian Klaver : > On 02/24/2015 07:10 AM, Guillaume Drolet wrote: > >> >> >> > >> >> So how did all this stuff get into the non-default tablespace in the >> first place? >> >> >> Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files >> mentionned in m

Re: [GENERAL] Sequences not moved to new tablespace

2015-02-24 10:39 GMT-05:00 Albe Laurenz : > Guillaume Drolet wrote: > >> If you want to move a whole database to a different tablespace (the > only reason > >> I can think of for doing what you are trying to so), use the command > >> ALTER DATABASE ... SET TABLESPACE ... > > > Thanks Laurenz. I tr

[GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

Hi, Please look at my query : [shreyas@rails_app_test (master)]$ rails db psql (9.4.1) Type "help" for help. app_development=# select id, location, name from people; id | location | name +--+-- 2 | X| foo 3 | X| foo 4 | Y| foo (3 rows) app_developm

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

Hi 2015-02-24 17:02 GMT+01:00 Arup Rakshit : > Hi, > > Please look at my query : > > [shreyas@rails_app_test (master)]$ rails db > psql (9.4.1) > Type "help" for help. > > app_development=# select id, location, name from people; > id | location | name > +--+-- > 2 | X|

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

Arup Rakshit wrote: > This *FILTER* method is available from 9.4, How can I get the same output > below 9.4 version ? case when ... see: http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-pay-off/ Andreas -- Really, I'm not out to destroy Microsoft. That will just be a compl

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

On Tuesday, February 24, 2015 06:13:21 PM Pavel Stehule wrote: > Hi > > 2015-02-24 17:02 GMT+01:00 Arup Rakshit : > > > Hi, > > > > Please look at my query : > > > > [shreyas@rails_app_test (master)]$ rails db > > psql (9.4.1) > > Type "help" for help. > > > > app_development=# select id, locatio

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4?

On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote: > Arup Rakshit wrote: > > > This *FILTER* method is available from 9.4, How can I get the same output > > below 9.4 version ? > > > case when ... > > see: > http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-p

Re: [GENERAL] Row-level Security vs Application-level authz

On 2/23/15 8:16 PM, Stephen Frost wrote: > * David G. Johnston (david.g.johns...@gmail.com) wrote: >> I take it that the table has to be permanent otherwise you would have >> suggested >> and unlogged temporary table as the target... > > A temporary table would have to be recreated each time and t

[GENERAL]

CREATE AGGREGATE group_concat(text) ( SFUNC = _group_concat, STYPE = text ); is it availabe in oracle..?

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4?

On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote: > Arup Rakshit wrote: > > > This *FILTER* method is available from 9.4, How can I get the same output > > below 9.4 version ? > > > case when ... > > see: > http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-p

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

On 2/24/2015 11:29 AM, Arup Rakshit wrote: On Tuesday, February 24, 2015 06:13:21 PM Pavel Stehule wrote: Hi 2015-02-24 17:02 GMT+01:00 Arup Rakshit : Hi, Please look at my query : [shreyas@rails_app_test (master)]$ rails db psql (9.4.1) Type "help" for help. app_development=# select id, l

[GENERAL] newbie how to access the information scheme

Hello, I'd like to see all the tables in my data base, but can't figure out how to access th information scheme. 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] newbie how to access the information scheme

I normally do the command (in psql) \d+ But if you want an actual SQL statement, the above invokes: SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WH

Re: [GENERAL] newbie how to access the information scheme

On 24.2.2015 19:58, John McKown wrote: > I normally do the command (in psql) > > \d+ > > But if you want an actual SQL statement, the above invokes: > > SELECT n.nspname as "Schema", > c.relname as "Name", > CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' > THEN 'materiali

Re: [GENERAL] SQL solution for my JDBC timezone issue

On 25/02/15 04:29, Adrian Klaver wrote: On 02/24/2015 06:25 AM, George Woodring wrote: -- In your original post you mentioned that access to the databases is through a Web server. -- Is there just one Web server with one time zone? We have 2 web servers that are clustered together. They a

[GENERAL] 9.3: bug related to json

Hi, I think I found a json related bug in 9.3. Given this query: select * from json_array_elements('[{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]}, {"s":[{"x":"3"},{"x":"4"},{"x":"6"}]}]') t(el) cross join lateral ( select syms.sym ->> 'x' as x from jso

Re: [GENERAL] SQL solution for my JDBC timezone issue

On 02/24/2015 11:36 AM, Gavin Flower wrote: On 25/02/15 04:29, Adrian Klaver wrote: On 02/24/2015 06:25 AM, George Woodring wrote: -- In your original post you mentioned that access to the databases is through a Web server. -- Is there just one Web server with one time zone? We have 2 web

Re: [GENERAL]

On 02/24/2015 10:08 AM, Ramesh T wrote: CREATE AGGREGATE group_concat(text) ( SFUNC = _group_concat, STYPE = text ); is it availabe in oracle..? FYI, this is the Postgres list. You might have more success on an Oracle list. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via p

Re: [GENERAL] Row-level Security vs Application-level authz

* David Steele (da...@pgmasters.net) wrote: > On 2/23/15 8:16 PM, Stephen Frost wrote: > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > >> I take it that the table has to be permanent otherwise you would have > >> suggested > >> and unlogged temporary table as the target... > > > > A

Re: [GENERAL] Row-level Security vs Application-level authz

On 2/24/15 3:07 PM, Stephen Frost wrote: > * David Steele (da...@pgmasters.net) wrote: >> On 2/23/15 8:16 PM, Stephen Frost wrote: >>> * David G. Johnston (david.g.johns...@gmail.com) wrote: I take it that the table has to be permanent otherwise you would have suggested and unlogged

Re: [GENERAL] Row-level Security vs Application-level authz

* David Steele (da...@pgmasters.net) wrote: > On 2/24/15 3:07 PM, Stephen Frost wrote: > > The problem with a temporary table is, well, it goes away. :) There are > > further concerns that, because it's created in some fashion by the > > single application user, it might be less secure. Really, t

Re: [GENERAL] Row-level Security vs Application-level authz

On 2/24/15 3:31 PM, Stephen Frost wrote: > * David Steele (da...@pgmasters.net) wrote: >> On 2/24/15 3:07 PM, Stephen Frost wrote: >>> The problem with a temporary table is, well, it goes away. :) There are >>> further concerns that, because it's created in some fashion by the >>> single applicati

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

> > >> >> I tried, but it is not giving the output exactly like *FILTER*. >> >> app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id) >> ELSE 0 END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0 >> END AS Non_X_loc from people group by lower(location); >> x_loc

[GENERAL] Longest prefix matching CTE

Have an Oracle "connect by" SQL that looks something like : select phone, pfx, len, (select info from codes where pfx = x.pfx) infot from ( select :x phone, to_number(substr( :x, 1, length(:x)-level+1 )) pfx, length(:x)-level+1 len from dual connect by level <= length(:x) order by level

Re: [GENERAL] Longest prefix matching CTE

On Feb 24, 2015, at 3:50 PM, Tim Smith wrote: > > > The goal being to match the longest prefix given a full phone number, e.g. > > > 61234567890 would match "australia proper 61" > whilst > 61134567890 would match "Australia premium 6113" > and > 61894321010 would match "Australia - Sydney

Re: [GENERAL] Row-level Security vs Application-level authz

* David Steele (da...@pgmasters.net) wrote: > So I guess my last question is if you are inserting rows into a table to > track user connections, how do you clean them out when the client does > not disconnect cleanly? Or is this table intended to be append-only? It wouldn't be intended to be appe

Re: [GENERAL] Row-level Security vs Application-level authz

On Tue, Feb 24, 2015 at 8:37 PM, Stephen Frost wrote: > * David Steele (da...@pgmasters.net) wrote: >> So I guess my last question is if you are inserting rows into a table to >> track user connections, how do you clean them out when the client does >> not disconnect cleanly? Or is this table int

Re: [GENERAL] Row-level Security vs Application-level authz

Adam, * Adam Hooper (a...@adamhooper.com) wrote: > On Tue, Feb 24, 2015 at 8:37 PM, Stephen Frost wrote: > > * David Steele (da...@pgmasters.net) wrote: > >> So I guess my last question is if you are inserting rows into a table to > >> track user connections, how do you clean them out when the cl

Re: [GENERAL] 9.3: bug related to json

Torsten Förtsch wrote > cross join lateral ( > select string_agg(', ', syms.sym ->> 'x') as xx >from json_array_elements(t.el -> 's') > syms(sym) I'm doubting you intended to join a bunch of commas using the field value as the delimiter...methinks your got the argument or

Re: [GENERAL] 9.3: bug related to json

Torsten Förtsch wrote > Is there anything similar for JSON scalars? IDK, but have you tried "::text"? David J. -- View this message in context: http://postgresql.nabble.com/9-3-bug-related-to-json-tp5839261p5839311.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Row-level Security vs Application-level authz

> * David Steele (da...@pgmasters.net) wrote: > > So I guess my last question is if you are inserting rows into a table to > > track user connections, how do you clean them out when the client does > > not disconnect cleanly? Or is this table intended to be append-only? > > It wouldn't be intende

Re: [GENERAL] (unknown)

Ramesh T schrieb am 24.02.2015 um 19:08: > CREATE AGGREGATE group_concat(text) ( > SFUNC = _group_concat, > STYPE = text > ); > is it availabe in oracle..? https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 -- Sent via pgsql-general mailing list (pgsql-g