Update karlstaging with production db dump file

[Paul Everitt]

We need to update karlstaging's database with the dump from production. The dump file is in /home/ctheune/box/karldump. I would like to watch what you're doing (Google Hangout, Skype shared screen, etc.) to see if I can learn more about it. Also, we can consider updating the Fabric automation script, perhaps simplifying it to just point at an export file.

Notes from the exchange between Tres and gocept:

Tres wrote:
Also, has the sync script been adjusted to deal with the recurring
permission problems we have seen in the past following such a sync?

Christian replied:
To fix issues with permissions for good, I created a PostgreSQL
role/user named
"osf" (same as in production) that uses the same password as currently
used for
the staging role.

The next import should be run like this:

# stop appservers
$ sudo -u karlstaging -i
$ dropdb karlstaging
$ createdb -U osf -W karlstaging
$ pg_restore -eO -h localhost -U osf -W -n public -d karlstaging /home/ctheune/box/karldump/karl.sql
# start appservers

This should cause the ownership issues to be solved then.

[Chris Rossi (chris-archimedeanco)]

I don't entirely understand what's going on here:

$ pg_restore -f /home/ctheune/box/karldump/karl.sql
/usr/bin/pg_restore -eO -h
localhost -U karlstaging -W

Is that a a single command? Two commands? Why do we have the full path to pg_restore in one place, but just the bare name in another?

[Christian Theune (ctheune)]

Ouch. I think I mis-copy-and-pasted that.

[Christian Theune (ctheune)]

This should be it, I guess.

[Chris Rossi (chris-archimedeanco)]

Almost, left out a '-d':

crossi@karlstaging01 ~ $ pg_restore -e -O -h localhost -U osf -W -d karlstaging /home/ctheune/box/karldump/karl.sql

More importantly, this doesn't seem to solve the permissions issue:
crossi@karlstaging01 ~ $ pg_restore -e -O -h localhost -U osf -W -d karlstaging /home/ctheune/box/karldump/karl.sql
Password:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 716; 2612 11574 PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of language plpgsql
    Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;

[Christian Theune (ctheune)]

Sigh. I did what Stefan started to prepare for the dump/load and arranged it to fit the permissions and box handling.

Anyway, pg_restore can filter for schemata and I adapted above command to make it filter out everything but the public schema, which should do what you want. Hope this helps.

[Paul Everitt (paul-agendaless)]

Chris, as you go through this, can you make sure that the repozitory db is part of the dump and wired up correctly? You can do this by going to a wiki page (e.g. in the A Time For Testing community) and:

- Checking that the existing history is there
- Make a change and see if the history works ok

--Paul

On Jul 8, 2014, at 10:33 AM, Christian Theune <email address hidden> wrote:

> Sigh. I did what Stefan started to prepare for the dump/load and
> arranged it to fit the permissions and box handling.
>
> Anyway, pg_restore can filter for schemata and I adapted above command
> to make it filter out everything but the public schema, which should do
> what you want. Hope this helps.
>
> ** Description changed:
>
> We need to update karlstaging's database with the dump from production.
> The dump file is in /home/ctheune/box/karldump. I would like to watch
> what you're doing (Google Hangout, Skype shared screen, etc.) to see if
> I can learn more about it. Also, we can consider updating the Fabric
> automation script, perhaps simplifying it to just point at an export
> file.
>
> Notes from the exchange between Tres and gocept:
>
> Tres wrote:
> Also, has the sync script been adjusted to deal with the recurring
> permission problems we have seen in the past following such a sync?
>
> Christian replied:
> To fix issues with permissions for good, I created a PostgreSQL
> role/user named
> "osf" (same as in production) that uses the same password as currently
> used for
> the staging role.
>
> The next import should be run like this:
>
> # stop appservers
> $ sudo -u karlstaging -i
> $ dropdb karlstaging
> $ createdb -U osf -W karlstaging
> - $ pg_restore -eO -h localhost -U osf -W karlstaging /home/ctheune/box/karldump/karl.sql
> + $ pg_restore -eO -h localhost -U osf -W -d karlstaging /home/ctheune/box/karldump/karl.sql
> # start appservers
>
> This should cause the ownership issues to be solved then.
>
> ** Description changed:
>
> We need to update karlstaging's database with the dump from production.
> The dump file is in /home/ctheune/box/karldump. I would like to watch
> what you're doing (Google Hangout, Skype shared screen, etc.) to see if
> I can learn more about it. Also, we can consider updating the Fabric
> automation script, perhaps simplifying it to just point at an export
> file.
>
> Notes from the exchange between Tres and gocept:
>
> Tres wrote:
> Also, has the sync script been adjusted to deal with the recurring
> permission problems we have seen in the past following such a sync?
>
> Christian replied:
> To fix issues with permissions for good, I created a PostgreSQL
> role/user named
> "osf" (same as in production) that uses the same password as currently
> used for
> the staging role.
>
> The next import should be run like this:
>
> # stop appservers
> $ sudo -u karlstaging -i
> $ dropdb karlstaging
> $ createdb -U osf -W karlstaging
> - $ pg_restore -eO -h localhost -U osf -W -d karlstaging /home/ctheune/box/karldump/karl.sql
> + $ pg_restore -eO -h localhost -U osf -W -n public -d karlstaging /home/ctheune/box/karldump/karl.sql
> # start appservers
>
> This should caus...

[Chris Rossi (chris-archimedeanco)]

Ok, it seems to be chugging away now. Thanks for the help, Christian!

[Chris Rossi (chris-archimedeanco)]

Yep, repozitory is working.

[Paul Everitt (paul-agendaless)]

Is the load finished and karlstaging is back in business?

--Paul

On Jul 8, 2014, at 2:42 PM, Chris Rossi <email address hidden> wrote:

> Yep, repozitory is working.
>
>
> ** Changed in: karl3
> Status: In Progress => Fix Committed
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1338268
>
> Title:
> Update karlstaging with production db dump file
>
> Status in KARL3:
> Fix Committed
>
> Bug description:
> We need to update karlstaging's database with the dump from
> production. The dump file is in /home/ctheune/box/karldump. I would
> like to watch what you're doing (Google Hangout, Skype shared screen,
> etc.) to see if I can learn more about it. Also, we can consider
> updating the Fabric automation script, perhaps simplifying it to just
> point at an export file.
>
> Notes from the exchange between Tres and gocept:
>
> Tres wrote:
> Also, has the sync script been adjusted to deal with the recurring
> permission problems we have seen in the past following such a sync?
>
> Christian replied:
> To fix issues with permissions for good, I created a PostgreSQL
> role/user named
> "osf" (same as in production) that uses the same password as currently
> used for
> the staging role.
>
> The next import should be run like this:
>
> # stop appservers
> $ sudo -u karlstaging -i
> $ dropdb karlstaging
> $ createdb -U osf -W karlstaging
> $ pg_restore -eO -h localhost -U osf -W -n public -d karlstaging /home/ctheune/box/karldump/karl.sql
> # start appservers
>
> This should cause the ownership issues to be solved then.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/karl3/+bug/1338268/+subscriptions

[Chris Rossi (chris-archimedeanco)]

Yes.

On Tue, Jul 8, 2014 at 3:09 PM, Paul Everitt <email address hidden> wrote:

> Is the load finished and karlstaging is back in business?
>
> --Paul
>
> On Jul 8, 2014, at 2:42 PM, Chris Rossi <email address hidden> wrote:
>
> > Yep, repozitory is working.
> >
> >
> > ** Changed in: karl3
> > Status: In Progress => Fix Committed
> >
> > --
> > You received this bug notification because you are subscribed to the bug
> > report.
> > https://bugs.launchpad.net/bugs/1338268
> >
> > Title:
> > Update karlstaging with production db dump file
> >
> > Status in KARL3:
> > Fix Committed
> >
> > Bug description:
> > We need to update karlstaging's database with the dump from
> > production. The dump file is in /home/ctheune/box/karldump. I would
> > like to watch what you're doing (Google Hangout, Skype shared screen,
> > etc.) to see if I can learn more about it. Also, we can consider
> > updating the Fabric automation script, perhaps simplifying it to just
> > point at an export file.
> >
> > Notes from the exchange between Tres and gocept:
> >
> > Tres wrote:
> > Also, has the sync script been adjusted to deal with the recurring
> > permission problems we have seen in the past following such a sync?
> >
> > Christian replied:
> > To fix issues with permissions for good, I created a PostgreSQL
> > role/user named
> > "osf" (same as in production) that uses the same password as currently
> > used for
> > the staging role.
> >
> > The next import should be run like this:
> >
> > # stop appservers
> > $ sudo -u karlstaging -i
> > $ dropdb karlstaging
> > $ createdb -U osf -W karlstaging
> > $ pg_restore -eO -h localhost -U osf -W -n public -d karlstaging
> /home/ctheune/box/karldump/karl.sql
> > # start appservers
> >
> > This should cause the ownership issues to be solved then.
> >
> > To manage notifications about this bug go to:
> > https://bugs.launchpad.net/karl3/+bug/1338268/+subscriptions
>
> --
> You received this bug notification because you are a bug assignee.
> https://bugs.launchpad.net/bugs/1338268
>
> Title:
> Update karlstaging with production db dump file
>
> Status in KARL3:
> Fix Committed
>
> Bug description:
> We need to update karlstaging's database with the dump from
> production. The dump file is in /home/ctheune/box/karldump. I would
> like to watch what you're doing (Google Hangout, Skype shared screen,
> etc.) to see if I can learn more about it. Also, we can consider
> updating the Fabric automation script, perhaps simplifying it to just
> point at an export file.
>
> Notes from the exchange between Tres and gocept:
>
> Tres wrote:
> Also, has the sync script been adjusted to deal with the recurring
> permission problems we have seen in the past following such a sync?
>
> Christian replied:
> To fix issues with permissions for good, I created a PostgreSQL
> role/user named
> "osf" (same as in production) that uses the same password as currently
> used for
> the staging role.
>
> The next import should be run like this:
>
> # stop appservers
> $ sudo -u karlstaging -i
> $ dropdb karlstaging
> $ createdb -U osf -W karlstaging
> $ pg_restore -e...

[Paul Everitt (paul-agendaless)]

Still working on this (e.g. Fabric stuff) or can I mark it as "Fix Released" to close it out?

[Chris Rossi (chris-archimedeanco)]

Ah woops, let's make this in progress until I do that.

[Paul Everitt (paul-agendaless)]

Alas, we didn't get the blobs loaded correctly (or they weren't there in the first place). This:

https://karlstaging.gocept.com/communities/its-a-test/files/scan-9.jpeg/dl/ABarazaforKenyaFlyer.jpg?save=True

...produces:

OperationalError: ERROR: large object 102300438 does not exist

[Paul Everitt (paul-agendaless)]

In addition to looking at the blob issue, Chris is going to work this morning on testing the Fabric script on karlstaging.

[Chris Rossi (chris-archimedeanco)]

That blob thing is vexing. Looking at that new. The blobs are in the DB. Not sure why it can't find them.

[Chris Rossi (chris-archimedeanco)]

Ok, as far as the blob thing goes, relstorage uses a table, 'blob_chunk', that stores blobs in chunks. That table seems to have migrated just fine to staging. The contents of that table, however, aren't the actual bytes in the blob, but large object ids (loid) which are used, in turn, by Postgres for it's own large object abstraction. Postgres, internally, uses the table 'pg_catalog.pg_largeobject'. This is where the actual blob data is stored. In neither production or staging do I have permission to examine the contents of this table, so that's pretty much as far as I can get in my investigation:

osf-karl=> select chunk_num, chunk from blob_chunk where zoid=18378983 and tid=258179190583974604;
 chunk_num | chunk
-----------+----------
         0 | 58119681
(1 row)

osf-karl=> \d pg_largeobject
Table "pg_catalog.pg_largeobject"
 Column | Type | Modifiers
--------+---------+-----------
 loid | oid | not null
 pageno | integer | not null
 data | bytea |
Indexes:
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

osf-karl=> select count(*) from pg_largeobject where loid=58119681
osf-karl-> ;
ERROR: permission denied for relation pg_largeobject
osf-karl=>

[Christian Theune (ctheune)]

Ok, so the import went fine as we excluded the public schema. The OID thing is annoying because that means that we can't get meaningful easy-to-operate dumps ... ?!?

Weird. We haven't seen this issue before, have we, when we did run into permission problems? Sigh.

I'll review the permissions, again but I'm surprised all this is coming up anyway when we had operational dumps before. I'll need to check with Stefan as I'm bound elsewhere.

[Stefan Walluhn (stw-c)]

Just FYI: I will not manage to dive into this before Monday.

[Stefan Walluhn (stw-c)]

Sorry for the delay, beside a high workload it takes some time to put together all the open ends in this discussion.

Restoring the Karl database from a dump introduces a lot of issues regarding permissions. While I tried to solve them by using a postgres superuser called "sync" to restore the database Christian Theunes approach is to create a dump that is able to be restored without any superuser to reduce complexity and to have a more general solution that may also work for other Karl installations.

As far as I understand this discussion there are still permission problems and Chris Rossi is unable to debug them because of database restrictions.

@Chris: Being a member of the osfkarl resource group you are allowed to perform a 'sudo -iu postgres' on the VM osfkarl01 to change into the postgres context. You can make use of the 'psql' command to connect to the database as a postgres superuser. You should be able to finish your investigation.

I will need some rounds to re-join the discussion, so feel free to ask me further questions if I missed something.

[Paul Everitt (paul-agendaless)]

Based on Stefan's comment, back to Rossi.

[Chris Rossi (chris-archimedeanco)]

Hey guys,

The permissions thing isn't really the crux of the issue. The problem is OIDs don't seem to be preserved across dump/restore. I'm looking at the documentation for pg_dump and I see that there is a '--oids' flag. Did you use that?

Chris

[Stefan Walluhn (stw-c)]

Hi Chris,

I will prepare a new dump using the '--oids' flag.

In the past we finished several database syncs to staging using the superuser and we never make use of the '--oids' flag. I am wondering, if staging was in a clean state or if there was a hidden issue regarding the blobs all the time and we never noticed.

Stefan

[Paul Everitt (paul-agendaless)]

I believe, prior to our recent update, that blobs from the production database were working ok.

--Paul

On Jul 23, 2014, at 3:54 AM, Stefan Walluhn <email address hidden> wrote:

> Hi Chris,
>
> I will prepare a new dump using the '--oids' flag.
>
> In the past we finished several database syncs to staging using the
> superuser and we never make use of the '--oids' flag. I am wondering, if
> staging was in a clean state or if there was a hidden issue regarding
> the blobs all the time and we never noticed.
>
> Stefan
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1338268
>
> Title:
> Update karlstaging with production db dump file
>
> Status in KARL3:
> In Progress
>
> Bug description:
> We need to update karlstaging's database with the dump from
> production. The dump file is in /home/ctheune/box/karldump. I would
> like to watch what you're doing (Google Hangout, Skype shared screen,
> etc.) to see if I can learn more about it. Also, we can consider
> updating the Fabric automation script, perhaps simplifying it to just
> point at an export file.
>
> Notes from the exchange between Tres and gocept:
>
> Tres wrote:
> Also, has the sync script been adjusted to deal with the recurring
> permission problems we have seen in the past following such a sync?
>
> Christian replied:
> To fix issues with permissions for good, I created a PostgreSQL
> role/user named
> "osf" (same as in production) that uses the same password as currently
> used for
> the staging role.
>
> The next import should be run like this:
>
> # stop appservers
> $ sudo -u karlstaging -i
> $ dropdb karlstaging
> $ createdb -U osf -W karlstaging
> $ pg_restore -eO -h localhost -U osf -W -n public -d karlstaging /home/ctheune/box/karldump/karl.sql
> # start appservers
>
> This should cause the ownership issues to be solved then.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/karl3/+bug/1338268/+subscriptions

[Paul Everitt (paul-agendaless)]

Stefan, we can take this back on, but I believe the dump file has disappeared from karlstaging01:/home/ctheune/box

[Christian Theune (ctheune)]

Great. I managed to loose the command I dumped with. However. I think that we might be better off nowadays with a full dump
as the real osf user exists on the staging environment now and we could thus make a superuser dump.

I'll start this dump now. Restore will be a bit different (again) ...

[Paul Everitt (paul-agendaless)]

Made Shane nosy on this. Christian is doing a full dump again. Shane will investigate what is currently wrong with blob permissions.

[Christian Theune (ctheune)]

Alright. I created a fresh dump with the simple dump command:

pg_dump -c -Fc osf-karl -f /home/ctheune/box/karl/osfkarl.sql

I suggest you try to restore this into the same database name (osf-karl) and using pg_restore as the postgresql user. The osf owner user exists already.

[Paul Everitt (paul-agendaless)]

Thanks Christian for the work in #27 and the dump file you left in the directory. Shane, can you do a restore for karlstaging and see if the various blog tables etc. for relstorage pop out correctly? (Perhaps investigate what's broken first. Namely, the error I report in comment #13.

[Paul Everitt (paul-agendaless)]

Thanks to Chris, then Theune, this is now done.