Re: [GENERAL] Schemas, Roles & Search Path

[Tom Lane]

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 ROLE joe SET search_path=common, facilities, accounting;

The latter.  A session only absorbs ALTER ROLE SET settings for the
exact role you're logging in as.  (Otherwise there would be a need
for a conflict resolution rule, and it's pretty hard to see how that
would work in general for arbitrary settings.)  Role "inheritance"
applies to granted privileges only.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Schemas, Roles & Search Path

[Don Parris]

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 department within the organization.  I am thinking to
use schemas for the department-specific modules.  So, for example:
Schema COMMON module: contains relations for categories, contacts and a few
other items.
FACILITIES module: contains relations for facility management department,
which includes the physical security team.
HR module: HR-specific relations
ACCOUNTING module: Accounting relations - but other departments need access
to certain accounting functions.
The relations within each schema still need appropriate privilege settings,
of course.

I would use group and user roles to manage privileges throughout the
database, so:
Group roles for HR, Accounting, Facilities, Sales, etc.
User roles assigned to appropriate group roles

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 ROLE joe SET search_path=common, facilities, accounting;


I don't see any references to setting the search_path for group roles, so I
assume I need to set this at the user role level.  Is that correct?

My thinking on the design could be off base, but I think this is the
general approach I want.


Thanks!
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

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

[Tim Smith]

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] New Slave - timeline ERROR

[drum.lu...@gmail.com]

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 read only
connections

The problem happens when, after pg_basebackup, I change the recovery.conf
to replicate from live,  changing these 2 lines:

standby_mode = on

primary_conninfo = 'host=IP_TO_THE_OTHER_SLAVE port=5432
user=replicator application_name=replication_slave02'


I read the link you have sent: http://dba.stackexchange.com/a/53546/24393
I'm doing all exactly the same, but only one parameter is different:

time pg_basebackup --pgdata=$PGDATA --host=IP_OF_MASTER --port=5432
--username=replication --password --xlog-method=stream --format=plain
--progress --verbose

I'm not using --xlog-method=strem
I'm using:
ssh postgres@slave1 'pg_basebackup --pgdata=- --format=tar
--label=bb_master --progress --host=localhost --port=5432
--username=replicator *--xlog* | pv --quiet --rate-limit 100M' | tar -x
--no-same-owner

on http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html is:
-xlog

Using this option is equivalent of using -X with method fetch.
So... I have 2 TB of data. I wouldn't like to run the pg_basebackup again
with the option: --xlog-method=stream without have sure that is going to
work


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?

Thank you



Lucas Possamai

kinghost.co.nz


On 10 January 2016 at 06:06, bricklen  wrote:

> 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 is handy if you can pull it
> off. If you are doing it over the wire and using rsync, "-z" for
> compression is recommended. If you are doing the tar format of the
> pg_basebackup, you *must* have the master ship the WALs to the slave
> otherwise it won't be able to synchronize (the "stream" method ships WALs
> over the wire so the end result is a synchronized system.
>
> - I always run pg_basebackup from the slave I am building, for simplicity.
> - I create new slaves almost every day (we have thousands of databases)
> using a bash script and it almost much never fails. In essence it is a big
> wrapper around the pg_basebackup command (though we are using pg93 mostly).
>
> The base backup command that I run from the slave I am building:
> pg_basebackup --pgdata=$PGDATA --host=$MASTER_IP --port=$PGPORT
> --username=replication --no-password --xlog-method=stream --format=plain
> --progress --verbose
>
> The recovery.conf:
> standby_mode = 'on'
> primary_conninfo = 'user=replication host=$IP_OF_UPSTREAM_SLAVE_OR_MASTER
> port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
> recovery_target_timeline = 'latest'
> archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup
> /path/to/WALs %r'
> restore_command = 'cp /path/to/WALs/%f "%p" 2>>
> /your/PGDATA/path/pg_log/standby.log'
>

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

[Adrian Klaver]

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_name
'customer');

And I was subsequently able to query that table from PostgreSQL..

I tried to add the "OPTIONS" to the IMPORT FOREIGN SCHEMA and got an
error that "dbname" is not a valid parameter.


Got to thinking. What happens if you do CREATE SERVER and pass dbname as 
an OPTIONS at that point, then do IMPORT FOREIGN SCHEMA using the server?




Thanks,

Deven




--
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] New Slave - timeline ERROR

[bricklen]

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
>

If you are able to stop Postgres on the slave you are taking the base
backup from, you could do this:

1). Stop postgres on slave1
2). Rsync slave1 to slave2 to copy only the deltas.
3). When you start up slave2 the WALs that the master has shipped to slave2
should apply and bring your system up to consistency.

Re: [GENERAL] New Slave - timeline ERROR

[drum.lu...@gmail.com]

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 slave then But I'm afraid getting it back online and
get some other errors hehehehe =\







Lucas Possamai

kinghost.co.nz


On 10 January 2016 at 10:59, bricklen  wrote:

> 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 long time
>>
>
> 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).
>

Re: [GENERAL] Schemas, Roles & Search Path

[Melvin Davidson]

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
needed.

Finally, it is NOT a good idea to use UPPERCASE or CamelCase  for object
names in PostgreSQL, as PostgreSQL will naturally assume lowercase for all
objects
unless they are quoted. So probably you want schema names to be common,
facilities, etc.

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 to set the search path for each user individually?
> > ALTER ROLE joe SET search_path=common, facilities, accounting;
>
> The latter.  A session only absorbs ALTER ROLE SET settings for the
> exact role you're logging in as.  (Otherwise there would be a need
> for a conflict resolution rule, and it's pretty hard to see how that
> would work in general for arbitrary settings.)  Role "inheritance"
> applies to granted privileges only.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

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
you do not need the "stream" option.

Re: [GENERAL] New Slave - timeline ERROR

[drum.lu...@gmail.com]

*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 stopped or do it from the master after putting
> the master in backup mode (eg. executing "pg_start_backup('slave_
> backup')")


I didn't unterstand why doing RSYNC twice... sorry

Lucas



Lucas Possamai

kinghost.co.nz


On 10 January 2016 at 11:19, bricklen  wrote:

> 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 do a
> second rsync with the slave stopped or do it from the master after putting
> the master in backup mode (eg. executing "pg_start_backup('slave_backup')")
>
>

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

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@$MASTER_IP:/var/lib/postgresql/data
/var/lib/postgresql/data/ --exclude postmaster.pid


> I didn't unterstand why doing RSYNC twice... sorry
>

Unless the source db cluster you are rsync'ing from is stopped, there will
be changes to data files replicated from the master. The second rsync might
not be necessary given the WALs are shipping from the master to slave2.

Re: [GENERAL] New Slave - timeline ERROR

[drum.lu...@gmail.com]

>
> 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


On 10 January 2016 at 11:31, bricklen  wrote:

>
>
> 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@$MASTER_IP:
> /var/lib/postgresql/data /var/lib/postgresql/data/ --exclude
> postmaster.pid
>
>
>> I didn't unterstand why doing RSYNC twice... sorry
>>
>
> Unless the source db cluster you are rsync'ing from is stopped, there will
> be changes to data files replicated from the master. The second rsync might
> not be necessary given the WALs are shipping from the master to slave2.
>
>

Re: [GENERAL] New Slave - timeline ERROR

[drum.lu...@gmail.com]

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


On 10 January 2016 at 10:34, bricklen  wrote:

>
> On Sat, Jan 9, 2016 at 12:36 PM, drum.lu...@gmail.com <
> 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
> you do not need the "stream" option.
>

Re: [GENERAL] New Slave - timeline ERROR

[drum.lu...@gmail.com]

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


On 10 January 2016 at 10:53, bricklen  wrote:

> 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
>>
>
> If you are able to stop Postgres on the slave you are taking the base
> backup from, you could do this:
>
> 1). Stop postgres on slave1
> 2). Rsync slave1 to slave2 to copy only the deltas.
> 3). When you start up slave2 the WALs that the master has shipped to
> slave2 should apply and bring your system up to consistency.
>

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

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 long time
>

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).

Re: [GENERAL] New Slave - timeline ERROR

[bricklen]

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 do a
second rsync with the slave stopped or do it from the master after putting
the master in backup mode (eg. executing "pg_start_backup('slave_backup')")

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

[Adrian Klaver]

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_name
'customer');

And I was subsequently able to query that table from PostgreSQL..


Yes, dbname is a required option to FOREIGN TABLE:

https://github.com/EnterpriseDB/mysql_fdw
"The following parameters can be set on a MySQL foreign table object:

dbname: Name of the MySQL database to query. This is a mandatory option."



I tried to add the "OPTIONS" to the IMPORT FOREIGN SCHEMA and got an
error that "dbname" is not a valid parameter.

Thanks,

Deven

On Fri, Jan 8, 2016 at 11:38 PM, Deven Phillips
> wrote:

Apparently not, though I have done so in the past on PostgreSQL 9.4.
It appears to be related to the "schema" with which the foreign
table is associated:

mydb=# CREATE FOREIGN TABLE customer (
id BIGINT,
name VARCHAR(150),
parent_id BIGINT,
oracle_id BIGINT,
last_updated_time TIMESTAMP,
created_time TIMESTAMP) SERVER mysql;
CREATE FOREIGN TABLE
mydb=# SELECT * FROM customer;
ERROR:  failed to prepare the MySQL query:
Table 'public.customer' doesn't exist

Any suggestions would be greatly appreciated!

Deven


On Fri, Jan 8, 2016 at 10:26 AM, Adrian Klaver
> wrote:

On 01/08/2016 07:04 AM, Deven Phillips wrote:

Hi all,

  I installed the newly released PostgreSQL 9.5 this
morning and
compiled the latest mysql_fdw extension from EnterpriseDB. I
was able to
create the SERVER and USER MAPPING, but I cannot seem to get
IMPORT
FOREIGN SCHEMA to do anything. The command executes without
error, but
none of the table schemas are imported from the MySQL DB.
Does anyone
have any advice, links, documentation which might be of help?


Can you CREATE FOREIGN TABLE and use it?


Thanks in advance!

Deven



--
Adrian Klaver
adrian.kla...@aklaver.com 






--
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] New Slave - timeline ERROR

[bricklen]

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 is handy if you can pull it
off. If you are doing it over the wire and using rsync, "-z" for
compression is recommended. If you are doing the tar format of the
pg_basebackup, you *must* have the master ship the WALs to the slave
otherwise it won't be able to synchronize (the "stream" method ships WALs
over the wire so the end result is a synchronized system.

- I always run pg_basebackup from the slave I am building, for simplicity.
- I create new slaves almost every day (we have thousands of databases)
using a bash script and it almost much never fails. In essence it is a big
wrapper around the pg_basebackup command (though we are using pg93 mostly).

The base backup command that I run from the slave I am building:
pg_basebackup --pgdata=$PGDATA --host=$MASTER_IP --port=$PGPORT
--username=replication --no-password --xlog-method=stream --format=plain
--progress --verbose

The recovery.conf:
standby_mode = 'on'
primary_conninfo = 'user=replication host=$IP_OF_UPSTREAM_SLAVE_OR_MASTER
port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
recovery_target_timeline = 'latest'
archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup
/path/to/WALs %r'
restore_command = 'cp /path/to/WALs/%f "%p" 2>>
/your/PGDATA/path/pg_log/standby.log'

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

[Adrian Klaver]

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 repo is that there are repos for 
BSD, RH(), Debian, Ubuntu, etc. Without knowing which one you 
where talking about an answer would be difficult. At any rate I did 
provide an answer:


http://www.postgresql.org/message-id/56904585.5050...@aklaver.com

Short answer 9.5 is in the repos. Run apt-get update then install 
apt-get postgresql-9.5.




Apologies for the tone, but I did ask a very simple question !



--
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] New Slave - timeline ERROR

[bricklen]

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, this is for incremental copying from the upstream source.
Actually, you don't need the -r with -a (it is implied), and you can run it
first with --dry-run to see what it _would_ do.
If you are not shipping over the WAN, then omit the -z flag as you do not
need compression.

Re: [GENERAL] New Slave - timeline ERROR

[John R Pierce]

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

[Don Parris]

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:
> COMMON.table, then the table is located directly and search_path is not
> needed.
>


Melvin, thanks for that - confirms what I understand about schemas.


>
> Finally, it is NOT a good idea to use UPPERCASE or CamelCase  for object
> names in PostgreSQL, as PostgreSQL will naturally assume lowercase for all
> objects
> unless they are quoted. So probably you want schema names to be common,
> facilities, etc.
>
>
Thanks.  I was just capitalizing for the e-mail.  I never actually use
upper or camel case in my code.



> 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 to set the search path for each user individually?
>> > ALTER ROLE joe SET search_path=common, facilities, accounting;
>>
>> The latter.  A session only absorbs ALTER ROLE SET settings for the
>> exact role you're logging in as.  (Otherwise there would be a need
>> for a conflict resolution rule, and it's pretty hard to see how that
>> would work in general for arbitrary settings.)  Role "inheritance"
>> applies to granted privileges only.
>>
>> regards, tom lane
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

Re: [GENERAL] Schemas, Roles & Search Path

[Don Parris]

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 to set the search path for each user individually?
> > ALTER ROLE joe SET search_path=common, facilities, accounting;
>
> The latter.  A session only absorbs ALTER ROLE SET settings for the
> exact role you're logging in as.  (Otherwise there would be a need
> for a conflict resolution rule, and it's pretty hard to see how that
> would work in general for arbitrary settings.)  Role "inheritance"
> applies to granted privileges only.
>
> regards, tom lane
>

Thanks Tom, that helps!

Don
-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ 

GPG Key ID: F5E179BE

Re: [GENERAL] New Slave - timeline ERROR

[drum.lu...@gmail.com]

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/postgresql/data/ --exclude
>>> postmaster.pid
>>
>>
>> Ah ok! So this will do an incrementa, right? not supposed to copy ALL the
>> base/ again?
>>
>
> Yes, this is for incremental copying from the upstream source.
> Actually, you don't need the -r with -a (it is implied), and you can run
> it first with --dry-run to see what it _would_ do.
> If you are not shipping over the WAN, then omit the -z flag as you do not
> need compression.
>


-- 


Lucas Possamai

kinghost.co.nz

Re: [GENERAL] New Slave - timeline ERROR

[drum.lu...@gmail.com]

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 with, given
> that I can't think of any other commands to try.


I chose the pg_basebackup command just to not stop any database. It's out
of circumstances to stop even the slave one... sorry...

I really don't know what else to do. Have tried everything!

Lucas

On 10 January 2016 at 13:31, bricklen  wrote:

> Bottom-posting is the convention in the postgresql lists, and makes it
> easier to follow a long thread.
>
> On Sat, Jan 9, 2016 at 3:16 PM, drum.lu...@gmail.com  > wrote:
>
>> My servers are not in the same network. A new pg_backup would take 30
>> hours to complete as I use --rate-limit 100MB.
>
>
> If you had enough bandwidth, you could do some shell magic to parallelize
> the rsync commands, or use something like
> http://moo.nac.uci.edu/~hjm/parsync/ to do that. If you are limited by
> bandwidth, then a single rsync run is probably what you're stuck with.
>
>
>> I really need to put his server up! =\
>>
>
> If you were running zfs you could also take a snapshot of the fs and use
> that for your base backup, but I assume you would have mentioned that if it
> was an option.
>
>
>
>> I don't think that running a pg_basebackup one more time will solve the
>> problem, because I've already done that!
>> I could run actually, but the problem is that it takes 30h! hahahahah
>>
>
> What is the --pgdata=- in your original command? Are you perhaps in the
> wrong directory and not getting all the required files?
>
>
> I'm out of fresh ideas. The rsync command is what I would go with, given
> that I can't think of any other commands to try.
>
>
>
>>
>> *Have a look:*
>> http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html
>>
>> Note that there are some limitations in an online backup from the standby:
>>>
>>
>>
>> The backup history file is not created in the database cluster backed up.
>>> There is no guarantee that all WAL files required for the backup are
>>> archived at the end of backup. If you are planning to use the backup for an
>>> archive recovery and want to ensure that all required files are available
>>> at that moment, you need to include them into the backup by using -x
>>>  option.
>>>
>>
> You had that in your original command I believe.
>

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

[Regina Obe]

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 a Coc.

Quoted from her note:
"You implied in your first post that you would attract more contributors
with a CoC."

Let me say -- I do not think you will attract more contributors.  You may
in fact attract parasites.

The FreeBSD thread that is often mentioned as reason why you need a Coc is
this from Randi Harper.
http://blog.randi.io/2015/12/31/the-developer-formerly-known-as-freebsdgirl/

Randi has just blocked me on twitter after I complimented her on her nice
shallow evidence - http://imgur.com/a/UVKfZ   Perhaps questioning her
harassment claims identifies me as a troll.

I have spoken to one of her victims Roberto Rosario.  A great loving man
from what I can tell, and she has truly tried to destroy his credibility
and harassed him beyond end.  I feel so horrible that a woman claiming to
watch out for minorities and women in tech is disingenuous and will
probably laugh pushing me down, because I'm not poor and have educated
parents.

So please whatever you do, if you really feel you need a Coc, do not
choose this one or anything that looks like it:

http://contributor-covenant.org/

I'm seeing social bullies going at every project demanding they adopt this
garbage.

Who knows what their intention is, I can only imagine.


Thanks Regina,
PostGIS PSC member







-- 
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] Code of Conduct: Is it time?

[Gavin Flower]

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 itemize what you expect to achieve with a Coc.

Quoted from her note:
"You implied in your first post that you would attract more contributors
with a CoC."

Let me say -- I do not think you will attract more contributors.  You may
in fact attract parasites.

The FreeBSD thread that is often mentioned as reason why you need a Coc is
this from Randi Harper.
http://blog.randi.io/2015/12/31/the-developer-formerly-known-as-freebsdgirl/

Randi has just blocked me on twitter after I complimented her on her nice
shallow evidence - http://imgur.com/a/UVKfZ   Perhaps questioning her
harassment claims identifies me as a troll.

I have spoken to one of her victims Roberto Rosario.  A great loving man
from what I can tell, and she has truly tried to destroy his credibility
and harassed him beyond end.  I feel so horrible that a woman claiming to
watch out for minorities and women in tech is disingenuous and will
probably laugh pushing me down, because I'm not poor and have educated
parents.

So please whatever you do, if you really feel you need a Coc, do not
choose this one or anything that looks like it:

http://contributor-covenant.org/

I'm seeing social bullies going at every project demanding they adopt this
garbage.

Who knows what their intention is, I can only imagine.


Thanks Regina,
PostGIS PSC member


I was at the 2015 Australian Linux Conference (held in Auckland, NZ), 
when Sarah Sharp harangued Linus Torvalds for over 20 minutes. Linus 
remained calm and polite throughout, yet most people would have been 
obviously annoyed within the first 5 minutes.  As backround see: 
http://www.networkworld.com/article/2988850/opensource-subnet/linux-kernel-dev-sarah-sharp-quits-citing-brutal-communications-style.html


I think some people, unintentionally, set themselves up as a victim.

So I would agree that a Coc is likely only to lead to arguments. Take 
something innocuous like 'do not offend people' - sounds good, now 
politely  explain why someone's deeply held beliefs contradict reality!



Cheers,
Gavin





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general