[GENERAL] Schemas, Roles & Search Path

Hi all, I just want to check my understanding of schemas, search paths and roles in implementing my database design. Scenario: A database with various "modules" (groups of tables & views, etc.), some of which are shared in common across a given organization, but others are specific to a given dep

Re: [GENERAL] No postgresql-9.5-prefix ?

Adrian Any chance you could answer my original question now that I have demonstrated to you what I meant by the PG repo even if I committed the cardinal sin of not pointing you to the exact page on the PG website ? Apologies for the tone, but I did ask a very simple question !

Re: [GENERAL] Schemas, Roles & Search Path

Don Parris writes: > I *think* I want to set the search path on the group roles so that the > Facilities team can see the COMMON and FACILITIES schemas: > ALTER ROLE fm_users search_path=common, facilities, accounting; > > Or do I need to set the search path for each user individually? > ALTER ROL

Re: [GENERAL] No postgresql-9.5-prefix ?

On 01/09/2016 06:31 AM, Tim Smith wrote: Adrian Any chance you could answer my original question now that I have demonstrated to you what I meant by the PG repo even if I committed the cardinal sin of not pointing you to the exact page on the PG website ? Well the reason I ask for the exact re

Re: [GENERAL] Schemas, Roles & Search Path

Just a side note. the search_path only sets the priority for resolving table locations EG: First look in COMMON, then FACILITIES until the table name is found. However, if you prefix the table name with the actual schema EG: COMMON.table, then the table is located directly and search_path is not n

Re: [GENERAL] New Slave - timeline ERROR

On Fri, Jan 8, 2016 at 8:44 PM, drum.lu...@gmail.com wrote: Hi, I'm a bit too lazy to try suss out the exact reasons for your failure, but here is a reasonably thorough guide to set up replication: http://dba.stackexchange.com/a/53546/24393 A few tips: - Having the master ship WALs to the slaves

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

On 01/08/2016 08:45 PM, Deven Phillips wrote: I DID get a foreign table to work using the following: CREATE FOREIGN TABLE customer ( id BIGINT, name VARCHAR(150), parent_id BIGINT, oracle_id BIGINT, last_updated_time TIMESTAMP, created_time TIMESTAMP) SERVER mysql OPTIONS (dbname 'mydb', table_n

Re: [GENERAL] New Slave - timeline ERROR

Hi @bricklen. Thanks for your reply. I've been working on it for 20h =( So The master is currently shipping the WALs to the slave. When pg_basebackup has done, I got a successful log: postgres(iostreams)[10037]: 2016-01-09 00:07:26.604 UTC|10085|LOG: database system is ready to accept

Re: [GENERAL] PostgreSQL 9.5, mysql_fdw, and IMPORT FOREIGN SCHEMA question

On 01/08/2016 08:45 PM, Deven Phillips wrote: I DID get a foreign table to work using the following: CREATE FOREIGN TABLE customer ( id BIGINT, name VARCHAR(150), parent_id BIGINT, oracle_id BIGINT, last_updated_time TIMESTAMP, created_time TIMESTAMP) SERVER mysql OPTIONS (dbname 'mydb', table_n

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 12:36 PM, drum.lu...@gmail.com wrote: > Do you have any other ideia? Do you know if --xlog it's the problem and I > should re-run the pg_basebackup again with the *--xlog-method=stream* > option? > If the master is successfully ships WALs to the slave you are setting up y

Re: [GENERAL] New Slave - timeline ERROR

Hi, If the master is successfully ships WALs to the slave you are setting up > you do not need the "stream" option. yes.. the master is successfully shipping the WALs Is there anything else? Help, please hehehehe Lucas Possamai kinghost.co.nz

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 1:49 PM, drum.lu...@gmail.com wrote: > Hi, > > If the master is successfully ships WALs to the slave you are setting up >> you do not need the "stream" option. > > > yes.. the master is successfully shipping the WALs > > Is there anything else? Help, please hehehehe >

Re: [GENERAL] New Slave - timeline ERROR

Hi, If you are able to stop Postgres on the slave you are taking the base > backup from, you could do this: I'm not... the data base is 2 TB. So, a RSYNC would take DAYS. And I'm not able to stop the SLAVE for that long time Lucas Lucas Possamai kinghost.co.nz

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 1:54 PM, drum.lu...@gmail.com wrote: > Hi, > > If you are able to stop Postgres on the slave you are taking the base >> backup from, you could do this: > > > I'm not... the data base is 2 TB. > So, a RSYNC would take DAYS. And I'm not able to stop the SLAVE for > that

Re: [GENERAL] New Slave - timeline ERROR

Hmm... I see... > > >> Depending on when you took the base backup and how many changes have >> occurred at your source (slave1) database cluster, the rsync execution time >> may or may not take as long as a new base backup if is only only shipping >> deltas (changed files). > > I could stop the sla

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:10 PM, drum.lu...@gmail.com wrote: > I could stop the slave then But I'm afraid getting it back online and > get some other errors > At this point I think your options are slim. If you are feeling adventurous, you can try doing the rsync with the slave running, then

Re: [GENERAL] New Slave - timeline ERROR

*rsync would be something like:* from slave1: rsync -av data/pg_xlog slave2:/var/lib/postgresql/data/ Is that correct? At this point I think your options are slim. If you are feeling > adventurous, you can try doing the rsync with the slave running, then do a > second rsync with the slave stoppe

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:22 PM, drum.lu...@gmail.com wrote: > *rsync would be something like:* > > from slave1: > rsync -av data/pg_xlog slave2:/var/lib/postgresql/data/ > Normally I run something like the following from the slave I am setting up. rsync -azr --progress --partial postgres@$MASTE

Re: [GENERAL] New Slave - timeline ERROR

> > rsync -azr --progress --partial postgres@$MASTER_IP: > /var/lib/postgresql/data/var/lib/postgresql/data/ --exclude > postmaster.pid Ah ok! So this will do an incrementa, right? not supposed to copy ALL the base/ again? Lucas Possamai kinghost.co.nz

Re: [GENERAL] New Slave - timeline ERROR

On Sat, Jan 9, 2016 at 2:35 PM, drum.lu...@gmail.com wrote: > rsync -azr --progress --partial postgres@$MASTER_IP: >> /var/lib/postgresql/data/var/lib/postgresql/data/ --exclude >> postmaster.pid > > > Ah ok! So this will do an incrementa, right? not supposed to copy ALL the > base/ again? > Yes

Re: [GENERAL] New Slave - timeline ERROR

Should I point of replication new slave to same DB? Lucas On Sunday, 10 January 2016, bricklen wrote: > On Sat, Jan 9, 2016 at 2:35 PM, drum.lu...@gmail.com > < > drum.lu...@gmail.com > > wrote: > >> rsync -azr --progress --partial postgres@$MASTER_IP: >>> /var/lib/postgresql/data/var/lib/post

Re: [GENERAL] New Slave - timeline ERROR

On 1/9/2016 4:33 PM, drum.lu...@gmail.com wrote: Should I point of replication new slave to same DB? I can't even guess what you're asking here. -- john r pierce, recycling bits in santa cruz

Re: [GENERAL] Schemas, Roles & Search Path

On Sat, Jan 9, 2016 at 11:16 AM, Melvin Davidson wrote: > Just a side note. the search_path only sets the priority for resolving > table locations > EG: First look in COMMON, then FACILITIES until the table name is found. > > However, if you prefix the table name with the actual schema EG: > COMM

Re: [GENERAL] Schemas, Roles & Search Path

On Sat, Jan 9, 2016 at 10:22 AM, Tom Lane wrote: > Don Parris writes: > > I *think* I want to set the search path on the group roles so that the > > Facilities team can see the COMMON and FACILITIES schemas: > > ALTER ROLE fm_users search_path=common, facilities, accounting; > > > > Or do I need

Re: [GENERAL] New Slave - timeline ERROR

What is the --pgdata=- in your original command? Are you perhaps in the > wrong directory and not getting all the required files? I run the pg_basebackup from the Slave on /var/lib/pgsql/9.2/data. So I'm not in the wrong directory... I'm out of fresh ideas. The rsync command is what I would go w

Re: [GENERAL] Code of Conduct: Is it time?

Josh informed me you guys are thinking about a CoC. Let me start off by saying that I don't think you need one and in fact having one may be dangerous. I fear for your safety. I think Roxanne mentioned some good points in an earlier thread that you should itemize what you expect to achieve with

Re: [GENERAL] Code of Conduct: Is it time?

On 10/01/16 20:37, Regina Obe wrote: Josh informed me you guys are thinking about a CoC. Let me start off by saying that I don't think you need one and in fact having one may be dangerous. I fear for your safety. I think Roxanne mentioned some good points in an earlier thread that you should i