Re: [GENERAL] cluster question

[Alex Samad]

On 15 August 2017 at 16:35, Andreas Kretschmer 
wrote:

>
>
> Am 15.08.2017 um 05:15 schrieb Alex Samad:
>
>> Hi
>>
>> Quick question.  I have a 2 node cluster - each node has its own ip.
>>
>> But from reading this, I really need a 3rd ip, which potentially floats
>> between the nodes to which ever is the master / rw node.
>>
>> Is that right? Sort of makes sense I guess
>>
>>
> That's one way to solve that problem. If you are using repmgr/repmgrd you
> can call own commands on events like failover. And, if you are using
> pgbouncer or connection-pooling you can change the config for pgbouncer
> (redefine the databases and there connection strings) and reload pgbouncer.
> You can do that with the event-notification commands defined in your
> repmgr-config.
> Other solution: with some Java-drivers you can define several databases
> and checks (if the database read-only or rw), the next PostgreSQL 10 will
> offer a similar feature.
>
>
okay think I have it setup, but when i do a switch over it gets stuck here.



NOTICE: STANDBY PROMOTE successful
NOTICE: Executing pg_rewind on old master server
NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl  -w -D
/var/lib/pgsql/9.6/data -m fast restart'
pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
Is server running?
starting server anyway
NOTICE: STANDBY FOLLOW successful


doesn't look like the output from https://github.com/
2ndQuadrant/repmgr/blob/master/README.md

NOTICE: STANDBY PROMOTE successful
NOTICE: Executing pg_rewind on old master server
NOTICE: 5 files copied to /var/lib/postgresql/9.5/data
NOTICE: restarting server using '/usr/local/bin/pg_ctl -w -D
/var/lib/postgresql/9.5/node_1/data -m fast restart'
pg_ctl: PID file "/var/lib/postgresql/9.5/node_1/data/postmaster.pid"
does not exist
Is server running?
starting server anyway
NOTICE: node 1 is replicating in state "streaming"
NOTICE: switchover was successful






> PS.: please don't top-posting.
>
>
>



> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Begginers question

[Alex Samad]

Hi

So I have been playing with an streaming  cluster. I have the replication
working I believe.

But whilst attempting to do an import of my original DB, I filled up my
disk pg_xlog directory.

Strangley I have tried this before and not filled this up.

so
1) why did it fill up this time and not previously
I add this
archive_command = '/bin/true'
wal_keep_segments = 1000 # <<< I'm guessing its this

2) how do I fix up, can I just remove the files from the pg_xlog directory

A

Re: [GENERAL] WAL replication wrong collate

[Michael Paquier]

On Tue, Aug 15, 2017 at 4:45 AM, basti  wrote:
> i have fixed. pg_update has create a wrong cluster

Let's be sure that we are not talking about a bug here, because you
are giving no details so it is hard to know if what you are seeing is
caused by an incorrect operation, or if that's an actual bug. So,
could you answer the following questions?
1) Are you referring to pg_upgrade instead of pg_update?
2) How did you do the upgrade of the standby server?
3) What are the original version of the server and the new version?
4) What is the exact order of the operations you did that made the
state you saw happen?
-- 
Michael


-- 
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] WAL replication wrong collate

[basti]

i have fixed. pg_update has create a wrong cluster


On 14.08.2017 20:52, basti wrote:

Hello,

i try to replicate my database. what i have done?

- create a cluster on slave (UTF8, en_US.utf8 collate/c_type)
- stop cluster and cleanup datadir
- do basebackup from master
- start db-cluster

Master has utf8, en_us.uft8 collate/c_type
Now my db on slave has UTF8, c.utf8 collate/c_type.
Whats wrong here?

master and slave had set the same locales.
I dont unterstand that i can create a database in en_us.utf8 and then 
when i did the basebackup it's change to c locale.

I cant find any option for pg_basebackup to set locale/collate.
I use this howto 
https://wiki.postgresql.org/wiki/Streaming_Replication and had install 
an other cluster in the past with successful.


How can help please?

Best regards,
basti







--
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] How to delete default privileges

[Jeff Janes]

On Tue, Aug 15, 2017 at 3:02 PM, Francisco Reyes  wrote:

> I have a DB where we changed ownership of all objects.
>
> We had:
>
> ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON tablesTO
> dbgroup_ro_group;
> ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON sequences TO
> dbgroup_ro_group;
>
> ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT, UPDATE,
> DELETE, INSERT ON tablesTO dbgroup_rw_group;
> ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT, USAGE
> ON sequences TO dbgroup_rw_group;
> ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT EXECUTE
> ON functions TO dbgroup_rw_group;
>
>
> But now there is a new DB owner and I have run the above, but with
> new_dbowner. How can I delete the old default grants? Can't find examples
> and don't see it on the documentation either.
>
>
> Any help would be greatly appreciated.


You just reverse them, changing GRANT...TO into REVOKE...FROM.  When the
altered-default is the same thing as the default-default, then the
altered-default disappears.

Cheers,

Jeff

[GENERAL] How to delete default privileges

[Francisco Reyes]

I have a DB where we changed ownership of all objects.

We had:

ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON tables
TO dbgroup_ro_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT ON sequences 
TO dbgroup_ro_group;


ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT, UPDATE, 
DELETE, INSERT ON tablesTO dbgroup_rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT SELECT, 
USAGE  ON sequences TO dbgroup_rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE old_dbowner GRANT 
EXECUTEON functions TO dbgroup_rw_group;



But now there is a new DB owner and I have run the above, but with 
new_dbowner. How can I delete the old default grants? Can't find 
examples and don't see it on the documentation either.



Any help would be greatly appreciated.



--
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] Corrupt index

[Daryl Stultz]

On 8/15/2017 1:07 PM, Andreas Kretschmer wrote:
>> I only wanted to exclude it. Anyway, you should install the latest patches.

>he can't, with RDS, he's at the mercy of Amazon for all sysadmin duties.

For many things, yes. I do have options to upgrade PG. We could probably do a 
minor upgrade with no concern. I can go to 9.3.16/17. I could also go to 
9.4.9/11/12.

/Daryl

Re: [GENERAL] Corrupt index

[John R Pierce]

On 8/15/2017 1:07 PM, Andreas Kretschmer wrote:

I only wanted to exclude it. Anyway, you should install the latest patches.


he can't, with RDS, he's at the mercy of Amazon for all sysadmin duties.


--
john r pierce, recycling bits in santa cruz



--
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] Corrupt index

[Andreas Kretschmer]

On 15 August 2017 21:54:56 GMT+02:00, Daryl Stultz  
wrote:
>
>>>We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a
>
>
>>Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1,
>i'm not sure).
>
>Hi Andreas, not that I'm aware of. I issue "reindex table mytable" to
>get out of the bad situation. I'm not sure if you are saying this bug
>could be getting me into the situation. If you don't mean that I am
>manually executing reindex concurrently, how do I go about determining
>if the system is using that procedure? Perhaps after some transaction,
>the system rebuilds the index automatically using "concurrently" and
>that is buggy/failing?
>
>Thanks.
>
>/Daryl

I only wanted to exclude it. Anyway, you should install the latest patches.


Regards, Andreas.


-- 
2ndQuadrant - The PostgreSQL Support Company


-- 
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] Corrupt index

[Daryl Stultz]

>>We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a


>Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1, i'm not 
>sure).

Hi Andreas, not that I'm aware of. I issue "reindex table mytable" to get out 
of the bad situation. I'm not sure if you are saying this bug could be getting 
me into the situation. If you don't mean that I am manually executing reindex 
concurrently, how do I go about determining if the system is using that 
procedure? Perhaps after some transaction, the system rebuilds the index 
automatically using "concurrently" and that is buggy/failing?

Thanks.

/Daryl

Re: [GENERAL] Corrupt index

[Daryl Stultz]

On Tue, Aug 15, 2017 at 12:24 PM, Daryl Stultz


>>You've given no details at all. What business pattern? What does the
>>index and table look like?

Hi Peter. There are no details that are obvious to me that I should be 
providing. The business process is one of a thousand that the application 
performs. To describe it generically, if we were running a commerce website, 
every time we find ourselves in this situation the record is related to a 
cancelled order. Something like that.

The primary key of the table is of "serial" type with an index like so:
 "scheduledassignments_pk" PRIMARY KEY, btree (scheduledassignmentid)

We have various triggers on the table.

Since the DB is managed by Amazon, I'm inclined to think the system is set up 
properly and the hardware is solid. This leaves me to consider some pattern in 
SQL, transactions and such that we are running that might be causing the 
corruption. Basically I don't have any idea what to look for.

/Daryl

Re: [GENERAL] Corrupt index

[Andreas Kretschmer]

On 15 August 2017 21:24:29 GMT+02:00, Daryl Stultz  
wrote:
>Hello,
>
>We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a
>


Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1, i'm not 
sure).


Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company


-- 
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] Corrupt index

[Peter Geoghegan]

On Tue, Aug 15, 2017 at 12:24 PM, Daryl Stultz
 wrote:
> We have a recurring problem that we can get out of with "reindex table
> mytable". Basically we can't find a particular record or two using the
> primary key and after reindexing we can. There does seem to be a pattern
> related to application business processes but we are at a loss as to how
> this could happen.

You've given no details at all. What business pattern? What does the
index and table look like?

-- 
Peter Geoghegan


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

[GENERAL] Corrupt index

[Daryl Stultz]

Hello,

We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a Multi-zone 
failover configuration. We do not have access to the hardware or file system. 
Fsync is enabled.

We have a recurring problem that we can get out of with "reindex table 
mytable". Basically we can't find a particular record or two using the primary 
key and after reindexing we can. There does seem to be a pattern related to 
application business processes but we are at a loss as to how this could happen.

Assuming the RDS instance is solid, what are the things we could do using 
nothing but SQL that could cause a corruption to the primary key index? I've 
researched the issue a fair amount and most content talks about hardware, file 
system, and fsync. As mentioned above fsync (and other properties) we can 
control (and fsync is enabled) but we can't get to the file system or hardware. 
If such a thing cannot be done with transactions, statements, and queries 
alone, what should we be looking at for settings?

Thanks.



--

Daryl Stultz
Principal Software Developer
_
OpenTempo, Inc
http://www.opentempo.com
mailto:daryl.stu...@opentempo.com

Re: [GENERAL] cluster question

[Andreas Kretschmer]

Am 15.08.2017 um 05:15 schrieb Alex Samad:

Hi

Quick question.  I have a 2 node cluster - each node has its own ip.

But from reading this, I really need a 3rd ip, which potentially 
floats between the nodes to which ever is the master / rw node.


Is that right? Sort of makes sense I guess



That's one way to solve that problem. If you are using repmgr/repmgrd 
you can call own commands on events like failover. And, if you are using 
pgbouncer or connection-pooling you can change the config for pgbouncer 
(redefine the databases and there connection strings) and reload 
pgbouncer. You can do that with the event-notification commands defined 
in your repmgr-config.
Other solution: with some Java-drivers you can define several databases 
and checks (if the database read-only or rw), the next PostgreSQL 10 
will offer a similar feature.


PS.: please don't top-posting.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



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