Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions

2017-10-31 Thread Arjen Nienhuis
On Nov 1, 2017 02:41, "Stephen Froehlich" wrote: Hi Michael, So if I'm reading this correctly, the proper way to do my use case is to use partitions of partitions, right? Or maybe reverse the order of the columns: PARTITION BY RANGE (source_no, start_time) --Stephen -Original Message--

Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions

2017-10-31 Thread Stephen Froehlich
Hi Michael, So if I'm reading this correctly, the proper way to do my use case is to use partitions of partitions, right? --Stephen -Original Message- From: Michael Paquier [mailto:michael.paqu...@gmail.com] Sent: Tuesday, October 31, 2017 4:06 PM To: Stephen Froehlich Cc: pgsql-gener

Re: [GENERAL] pgaduit - is there a way to audit a role

2017-10-31 Thread David Steele
On 10/30/17 6:48 PM, rakeshkumar464 wrote: > Is there a way to audit a group like as follows > > alter role db_rw set pgaudit.log = 'read,write,function,ddl' > > and then any user part of db_rw role can be audited automatically. It does > not seem to work if I connect to the db as rakesh who i

Re: [GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions

2017-10-31 Thread Michael Paquier
On Tue, Oct 31, 2017 at 8:36 PM, Stephen Froehlich wrote: > CREATE TABLE lotsa_data_20171027_src3 PARTITION OF lotsa_data > FOR VALUES FROM ('2017-10-26 18:00:00-06', 3) TO ('2017-10-27 > 17:59:59.999-06', 3); > ERROR: partition " lotsa_data_20171027_src1" would overlap partition > "lotsa_data

[GENERAL] PG 10 - Trouble with overlap of range partition of two dimensions

2017-10-31 Thread Stephen Froehlich
So I have a table that has two fields I want to partition by: CREATE TABLE lotsa_data ( start_time timestamp with time zone, source_nointeger, counter integer) PARTITION BY RANGE (start_time, source_no); CREATE TABLE lotsa_data_20171027_src1 PARTITION OF lotsa_data FOR VALUES FROM

Re: [GENERAL] query not scaling

2017-10-31 Thread Rob Sargent
On 10/31/2017 03:12 AM, Laurenz Albe wrote: Rob Sargent wrote: I think your biggest problem is the join condition on m.basepos between s.startbase and s.endbase That forces a nested loop join, which cannot be performed efficiently. Agree! 800,000 * 4,000 = 3,200,000,000. It's just that I

Re: [GENERAL] Roles inherited from a role which is the owner of a database can drop it?

2017-10-31 Thread Stephen Frost
Greetings, * Ivan Voras (ivo...@gmail.com) wrote: > On 30 October 2017 at 22:10, David G. Johnston > wrote: > > ​Not quite following but ownership is an inheritable permission; > > Basically, I'm asking if "ownership" can be revoked from the set of > inherited permissions? If there is a role G w

Re: [GENERAL] pg_audit to mask literal sql

2017-10-31 Thread Stephen Frost
Greetings, * rakeshkumar464 (rakeshkumar...@outlook.com) wrote: > By mask I mean pgaudit should log where ssn = '123-456-7891' as where ssn = > '?' Data masking really isn't part of auditing, and so even if pgaudit could do so, that wouldn't really be the right place to make it happen. There ha

Re: [GENERAL] Backup strategy using 'wal_keep_segments'

2017-10-31 Thread Stephen Frost
Greetings, * Rhhh Lin (ruanline...@hotmail.com) wrote: > I would actually be an advocate for using a proper archive_command in order > to facilitate a proper (Per the documentation) PITR and backup strategy. Glad to hear it. > However, a colleague had suggested such a creative approach (Possibl

Re: [GENERAL] Backup strategy using 'wal_keep_segments'

2017-10-31 Thread Michael Paquier
On Tue, Oct 31, 2017 at 9:53 AM, Rhhh Lin wrote: > I would actually be an advocate for using a proper archive_command in order > to facilitate a proper (Per the documentation) PITR and backup strategy. You should avoid using your own fancy archive command. There are things that WAL-E for this pur

Re: [GENERAL] Backup strategy using 'wal_keep_segments'

2017-10-31 Thread Rhhh Lin
Hi Stephen, Thanks for replying. I would actually be an advocate for using a proper archive_command in order to facilitate a proper (Per the documentation) PITR and backup strategy. However, a colleague had suggested such a creative approach (Possibly, less administrative overhead, ease of m

Re: [GENERAL] How to access a second database

2017-10-31 Thread John R Pierce
On 10/31/2017 12:41 AM, John R Pierce wrote: if you're doing a lot of this, why not use two schema in the same database?  then its just ...schema.table... otherwise, you need to use FDW and foreign tables. see https://www.postgresql.org/docs/current/static/postgres-fdw.html oh, I sh

Re: [GENERAL] How to access a second database

2017-10-31 Thread John R Pierce
On 10/31/2017 12:15 AM, Sherman Willden wrote: I am trying to access a table from another database. I have the permissions to create under my own login. I have performed the following so far: sherman@sql-dev: createdb sandbox01 sherman@sql-dev:~$ createdb sandbox02. After logging into sandbox02

Re: [GENERAL] How to access a second database

2017-10-31 Thread Stanislav Ganin
Hello Sherman, You cannot cross reference databases like this in PG. You should re-think the schema you are going to use. Check this line from the documentation (https://www.postgresql.org/docs/current/static/ddl-schemas.html): “A PostgreSQL database cluster contains one or more named databases.

[GENERAL] How to access a second database

2017-10-31 Thread Sherman Willden
I am trying to access a table from another database. I have the permissions to create under my own login. I have performed the following so far: sherman@sql-dev: createdb sandbox01 sherman@sql-dev:~$ createdb sandbox02. After logging into sandbox02 I performed the following: sandbox02=# CREATE TABL