870535 - PostgreSQL database user/pass for production PaaS

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Chris Turra [:cturra]]

similar to bug 869201, we need a user created on the database servers (pgdb[12].paas.scl3.mozilla.com). the following outlines the details for user creation and grants:

  http://docs.stackato.com/cluster/external-db.html#postgresql


access will be through zeus, so needs to allow connections from 10.22.93.208 - 10.22.93.213.

Comment 1

[Sheeri Cabral [:sheeri]]

They also need to be setup in a master/slave configuration. I'm not sure puppet worked right on pgdb2 to be honest.

Comment 2

[Matt Pressman [:mpressman]]

create user stackato with createdb createrole createuser login password 'PASSWORD REMOVED'

I have done this and given the password to cturra.

Comment 3

[Matt Pressman [:mpressman]]

To set these up as a master/replica I will use the new postgres puppet module and do you care about the postgresql version? They are currently using 9.0, but if possible, I would suggest using 9.2, or at least 9.1. Let me know and I will upgrade them while enabling replication.

Comment 4

[Chris Turra [:cturra]]

(In reply to Matt Pressman [:mpressman] from comment #3)
> To set these up as a master/replica I will use the new postgres puppet
> module and do you care about the postgresql version? They are currently
> using 9.0, but if possible, I would suggest using 9.2, or at least 9.1. Let
> me know and I will upgrade them while enabling replication.

i would say we go with the latest!

i have also gone ahead and completed the external postgresql service configurations within stackato.

stackato@paas:/root$ kato config get postgresql_node postgresql
 database: postgres
 host: paas-postgresql-vip.db.scl3.mozilla.com
 pass: <REMOVED>
 port: 5432
 user: stackato

Comment 5

[Sheeri Cabral [:sheeri]]

Matt, can you also upgrade the dbs themselves, if you haven't already done so? I think they're on 9.1.

Comment 6

[Matt Pressman [:mpressman]]

sounds good, I'll update them from the current 9.0.13, to the latest 9.2.4 using the new postgres module while enabling master/replica

Comment 7

[Sheeri Cabral [:sheeri]]

put this in puppet:

node "pgdb1.paas.scl3.mozilla.com" {
    class {
        'postgres::server':
            enable_replication        => true,
            enable_wal                => true,
            wal_replica_addr          => '10.22.93.22',
            replication_type          => 'master',
            replication_replica_addrs => [ '10.22.93.22', ],
            replication_master_user   => hiera('secrets_pg_paas_repl_username'),
            replication_user_password => hiera('secrets_pg_paas_repl_password'),
    }
}


node "pgdb2.paas.scl3.mozilla.com" {
    class {
        'postgres::server':
            enable_replication        => true,
            enable_wal                => true,
            wal_master_addr           => '10.22.93.21',
            replication_type          => 'slave',
            replication_master_addr   => '10.22.93.21',
    }
}


(haven't put any passwords in hiera)

ran puppet on pgdb1 and 2, that installed postgres 9.2

Then ran this on both machines:
$ initdb --lc-collate=en_US.UTF-8

And then this:
[postgres@pgdb1 ~]$   /usr/pgsql-9.2/bin/pg_upgrade -d /var/lib/pgsql/9.0/data/ -D /var/lib/pgsql/9.2/data/ -b /usr/pgsql-9.0/bin/ -B /usr/pgsql-9.2/bin/ -c

The -c is for checking, just in case. Then when everything was fine I ran this (same command, but without the -c)

[postgres@pgdb1 ~]$   /usr/pgsql-9.2/bin/pg_upgrade -d /var/lib/pgsql/9.0/data/ -D /var/lib/pgsql/9.2/data/ -b /usr/pgsql-9.0/bin/ -B /usr/pgsql-9.2/bin/

And the end of the output is:


Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

Comment 8

[Sheeri Cabral [:sheeri]]

[postgres@pgdb1 ~]$ ./delete_old_cluster.sh 
[postgres@pgdb1 ~]$ 

That's done.

Also did a yum remove of the old packages:

yum remove postgresql90-libs-9.0.13-1PGDG.rhel6.x86_64 postgresql90-9.0.13-1PGDG.rhel6.x86_64 postgresql90-devel-9.0.13-1PGDG.rhel6.x86_64 postgresql90-server-9.0.13-1PGDG.rhel6.x86_64

Comment 9

[Sheeri Cabral [:sheeri]]

OK, I also set up replication with a LOT of help from Matt (thanx!)

I set up the pg_hba.conf to allow the hosts as per http://docs.stackato.com/cluster/external-db.html#postgresql and restarted postgres.

The "kato" stuff is stuff that you have to do from the stackato hosts, I believe.

Comment 10

[Chris Turra [:cturra]]

(In reply to Sheeri Cabral [:sheeri] from comment #9)
> 
> The "kato" stuff is stuff that you have to do from the stackato hosts, I
> believe.

it has already been done. see comment 4 ;)

Comment 11

[Chris Turra [:cturra]]

i am going to need some assistance here. i am finishing up the final steps in the production paas (new deployment with the latest release). while doing so, i have run into a couple issues:

1) when i configure the postgresql service in stackato to use the postgresql zeus vip, i am unable to complete authentication with the postgresql client. what is curious here is the zeus configuration is identical (aside from monitoring) to the mysql vip that is working fine. additionally, i had :adam spend some time checking all the flows along the network and he came up empty handed. here is a little stdout from what i am seeing:

 connecting through the vip:
 ===

 stackato@stackato-core1:~$ psql -U stackato postgres -W -h paas-postgresql-vip.db.scl3.mozilla.com
 Password for user stackato:
 psql: stackato@stackato-core1:/home/cturra$

  ^ appears to be timing out, but netflows are in place:

 stackato@stackato-core1:~$ nc -zv paas-postgresql-vip.db.scl3.mozilla.com 5432
 Connection to paas-postgresql-vip.db.scl3.mozilla.com 5432 port [tcp/postgresql] succeeded!


 connecting directly:
 ===
 
 stackato@stackato-core1:~$ psql -U stackato postgres -W -h 10.22.93.21
 Password for user stackato:
 psql (9.1.9, server 9.2.4)
 WARNING: psql version 9.1, server version 9.2.
         Some psql features might not work.
 Type "help" for help.

 postgres-# \q



2) since i was having limited luck with the vip, i tried to configure stackato directly to use pgdb1.paas.scl3. connections are fine, but i am seeing load of the following warnings (through the stackato event logger):

[2013-11-07 10:52:29.141038] postgresql_node_free_1 - pid=10634 tid=98e1 fid=ae41   WARN -- PostgreSQL error: ERROR:  column "procpid" does not exist\nLINE 1: select * from (select procpid, datname, query_start, usename...\n                              ^\n



:mpressman - i'm looking for some assistance since this is clearly out of my league. feel free to ping me on irc to chat about it when you have a sec.

Comment 12

[Matt Pressman [:mpressman]]

can the stackato event logger be upgraded or modified? that error looks to be querying the pg_stat_activity view, but the column names have changed, the tell is the error on procpid which was renamed to just pid? I don't know how important that event logger is, but the log will continue to fill with these errors

Comment 13

[Matt Pressman [:mpressman]]

There must be something with the vip settings. It's not completing the authorization. By connecting directly and bypassing the vip, the server logs the following notice that a connection attempt has been made:
2013-11-07 23:13:11.465 GMT,,,7793,"",527c1e87.1e71,1,"",2013-11-07 23:13:11 GMT,,0,LOG,00000,"connection received: host=10.22.93.30 port=60286",,,,,,,,,""

After receiving the correct password, the server logs that authentication has been successful:
2013-11-07 23:13:11.467 GMT,"stackato","postgres",7793,"10.22.93.30:60286",527c1e87.1e71,2,"authentication",2013-11-07 23:13:11 GMT,5/78,0,LOG,00000,"connection authorized: user=stackato database=postgres",,,,,,,,,""

Now, connecting through the vip, the server logs the connection attempt:
2013-11-07 23:15:27.386 GMT,,,7819,"",527c1f0f.1e8b,1,"",2013-11-07 23:15:27 GMT,,0,LOG,00000,"connection received: host=10.22.93.211 port=16830",,,,,,,,,""

But it waits to receive the proper password, this is where it appears to hang. The server than logs:
2013-11-07 23:15:58.227 GMT,,,7819,"10.22.93.211:16830",527c1f0f.1e8b,2,"",2013-11-07 23:15:27 GMT,,0,LOG,08P01,"incomplete startup packet",,,,,,,,,""

This indicates that zeus is not transmitting the proper protocol for the password exchange.

Comment 14

[Matt Pressman [:mpressman]]

I have confirmed that the server is responding with a protocol violation error. The log output 08P01 is listed in the code as:
define ERRCODE_PROTOCOL_VIOLATION

which is a Class 08 - Connection Exception

Comment 15

[Chris Turra [:cturra]]

welp. after many many reviews of the zeus configuration i found out what the problem was! i had the 'internal protocol' set to generic server first instead of generic client first. after updating that auth is functioning as expected:

 stackato@stackato-services1:~$ psql -U stackato postgres -W -h paas-postgresql-vip.db.scl3.mozilla.com
 Password for user stackato:
 psql (9.1.10, server 9.2.4)
 WARNING: psql version 9.1, server version 9.2.
         Some psql features might not work.
 Type "help" for help.

 postgres=# \q


the final thing i need to sort out is if it's going to be possible to upgrade the psql client from 9.1 to 9.2 on the stackato nodes. while the upgrade should be trivial, i suspect we're going to run into issues due to how specific the postgresql config directory is named:

  /etc/postgresql/9.1/main/


i have reached out the vendor about this. 

for now, i am going to mark this bug as r/fixed, since the outstanding item is in my court. :mpressman - i may reach out to you on irc for some advise tho :)