PG9.1 migration to PG9.6, dump/restore issues

Hi Everyone, I am working on a migration from PG9.1 to PG9.6. Hoping some people can chime in on my plans as I am running into some restore issues. We are upgrading to a new version of PG and migrating to new hardware with RHEL 7, so I am planning on doing a dump and restore to get moved to th

Re: PG9.1 migration to PG9.6, dump/restore issues

On 9/12/18 6:55 AM, Scot Kreienkamp wrote: Hi Everyone, I am working on a migration from PG9.1 to PG9.6.  Hoping some people can chime in on my plans as I am running into some restore issues. We are upgrading to a new version of PG and migrating to new hardware with RHEL 7, so I am planning

Re: PG9.1 migration to PG9.6, dump/restore issues

Scot Kreienkamp writes: > Restore completes successfully, but I noticed that the schema > permissions are missing, possibly others as well (Is this a known > issue?). If you're talking about custom modifications you made to the permissions of the "public" schema in particular, then yeah, that won

Re: PG9.1 migration to PG9.6, dump/restore issues

On 09/12/2018 08:55 AM, Scot Kreienkamp wrote: Hi Everyone, I am working on a migration from PG9.1 to PG9.6.  Hoping some people can chime in on my plans as I am running into some restore issues. We are upgrading to a new version of PG and migrating to new hardware with RHEL 7, so I am plan

Select rows when all all ids of its children records matches

I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all posts which has tag id 1, 2 and 3 for example. How should I do this? I tried ALL, but it didn’t work. Those tag ids comes from UI by users, so I am looking for generic approach. Thanks, Arup

Re: Select rows when all all ids of its children records matches

Maybe this: select p.id, p.name from posts p, posts_tags pt, tags t where t.id in (1, 2, 3)   and t.id = pt.tag_id   and pt.post_id = p.id; On 09/12/2018 10:23 AM, Arup Rakshit wrote: I have a table posts(id, name), posts_tags(post_id, tag_id) and tags (id, name) ... I want to get all

Re: Select rows when all all ids of its children records matches

IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( tag ids ) Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 8:58 PM, Ron wrote: > > Maybe this: > select p.id, p.name > from posts p, > posts_tags pt, > tags t > where t.id in (1, 2, 3) > and t.id = pt

Re: Select rows when all all ids of its children records matches

On Wednesday, September 12, 2018, Arup Rakshit wrote: > IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 ( > tag ids ) > Build arrays and then use the “contains” operator. David J.

Re: Select rows when all all ids of its children records matches

Can you show me the SQL construction? Do I need to use `WITH`? Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 9:13 PM, David G. Johnston > wrote: > > On Wednesday, September 12, 2018, Arup Rakshit > wrote: > IN is OR, I want the AND logic. Select posts which h

Re: Select rows when all all ids of its children records matches

> On 12 Sep 2018, at 17:44, Arup Rakshit wrote: > > Can you show me the SQL construction? Do I need to use `WITH`? An option is to create a bit-wise OR and SUM the grouped results. If the result of these 3 bits is 7, than the post matches all three tags. select p.id, p.name from post p

Re: Select rows when all all ids of its children records matches

I tried : WITH posts_tags_cte AS ( SELECT post_id, array_agg(tag_id) as tags FROM posts_tags WHERE tag_id in (1, 2) GROUP BY post_id ) SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id WHERE posts_tags_cte.tags @> array[1, 2]::int8

Re: Select rows when all all ids of its children records matches

On 09/12/2018 10:08 AM, Arup Rakshit wrote: I tried : WITH posts_tags_cte AS (         SELECT post_id, array_agg(tag_id) as tags         FROM posts_tags         WHERE tag_id in (1, 2)         GROUP BY post_id ) SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id

Re: Select rows when all all ids of its children records matches

Hi Rob, I figured it out. thanks. It is giving correct data. > Aren't you looking for cte.tags = array[1,2]? posts_tags_cte has tags column, so I am using it. Thanks, Arup Rakshit a...@zeit.io > On 12-Sep-2018, at 9:47 PM, Rob Sargent wrote: > > > > On 09/12/2018 10:08 AM, Arup Rakshit

RE: PG9.1 migration to PG9.6, dump/restore issues

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Wednesday, September

RE: PG9.1 migration to PG9.6, dump/restore issues

> Did you see errors in the restore? > > In particular about not finding roles(users) for the permissions? > > I ask because I do not see in the above anything about dumping objects > global to the cluster. That would include roles. I use: > > pg_dumpall -g -f globals.sql > > See: > > https://www.p

RE: PG9.1 migration to PG9.6, dump/restore issues

Thanks Ron, glad to hear it worked and someone was successful at it. I’m on the right path then. Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com From: R

Re: Code of Conduct plan

I wrote: > Stephen Frost writes: >> We seem to be a bit past that timeline... Do we have any update on when >> this will be moving forward? >> Or did I miss something? > Nope, you didn't. Folks have been on holiday which made it hard to keep > forward progress going, particularly with respect t

constraint exclusion with a tsrange type

Hey everybody, I'm having trouble getting constraint exclusion to work on a table partitioned with a tsrange type. I've distilled it down to this: create table t ( id serial primary key, observed_window tsrange not null ); create index t_window on t(observed_window); create table p1 (like t

Re: PG9.1 migration to PG9.6, dump/restore issues

On 9/12/18 11:28 AM, Scot Kreienkamp wrote: Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: scot.kreienk...@la-z-boy.com -Original Message- From: Tom Lane [mailto:t...@s

Re: PG9.1 migration to PG9.6, dump/restore issues

On 9/12/18 11:30 AM, Scot Kreienkamp wrote: Did you see errors in the restore? In particular about not finding roles(users) for the permissions? I ask because I do not see in the above anything about dumping objects global to the cluster. That would include roles. I use: pg_dumpall -g -f globa

Re: constraint exclusion with a tsrange type

Ben Chobot writes: > Hey everybody, I'm having trouble getting constraint exclusion to work on a > table partitioned with a tsrange type. I've distilled it down to this: > create table t ( > id serial primary key, > observed_window tsrange not null > ); > create index t_window on t(observed_w

Re: scram-sha-256 authentication broken in FIPS mode

On Wed, Sep 12, 2018 at 07:24:24AM +0900, Michael Paquier wrote: > Good point. Such things have bitten in the past. Okay, then let's do > something about sha2_openssl.c only on HEAD for now then, which I am > fine to finish wrapping. I was looking at trying to commit this patch, however more nee