#pg_dump

Parse pg_dump Functions Into Their Own Files (GreenPlum / Postgres)

Here’s what we wanted: Each function from our GreenPlum (aka Postgres) database scripted out into it’s own file. I think after much hacking I’ve got something working that only requires a small amount of manual intervention.

For whatever reason PostgreSQL/GreenPlum doesn’t seem to have a native way to split objects into separate files. This functionality is super useful when moving the database…

Acelerando a importação de dados no PostgreSQL

Estes dias me mandaram um e-mail +/- assim:

A pergunta

Te escrevo no intuito de ter a sua opinião (se possível é claro) em relação a uma migração de um database em Postgres de um servidor para outro. Os hardwares não são similares, são hardwares distintos com arquitetura distinta. A base com aprox.. 250GB.

Inicialmente testamos um pump, mas como era de se esperar o restore passa de 72h para ser…

got this message:

Error: Connection reset by peer - SSL_connect (Errno::ECONNRESET) ``` which told me to check error log. did this to unhide hidden directories:

defaults write com.apple.finder AppleShowAllFiles YESkillall Finder

http://www.macworld.co.uk/how-to/mac-software/how-show-hidden-files-in-mac-os-x-finder-funter-3520878/

incorporating latest data.

from imac to aws to mbp.

pg_dump -Fc --verbose --no-acl --no-owner -c -h localhost -p 5432 libval_development > ~/Documents/20151110_2.dump

for restoring on mbp, tried this

postgres pg_restore --create --clean -d postgres /path/to/dbbackup.dump

this hung. so canceled and then tried this:

(1)

pg_restore -v -d libval_development -a ~/Documents/20151110_2.dump > ~/Documents/20151110_2.log 2>&1

this was almost instantaneous, took a few seconds. but db wasn't actually loaded. so,

rake db:drop; rake db:create; rake db:migrate

then tried again. it's hanging on flows.

(2)

pg_restore -v -d libval_development -a ~/Documents/20151110_2.dump > ~/Documents/20151110_2_2.log 2>&1

comparing the log files for each try: they seem to have similar errors.

meanwhile, on heroku,

heroku pg:backups restore 'https//aws...'

is hanging at 1.25GB. UPDATE: was a success, took around 20 minutes.

but the weirdest thing has happened for mbp: data for two tables (actual_productions and actual_sheets) has not been copied over. these are fairly recently created tables. there was some intricacy in these tables because their names were changed from two other tables that were then deleted. here is some of the log file:

pg_restore: processing data for table "actual_productions" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3408; 0 50939 TABLE DATA actual_productions allen pg_restore: [archiver (db)] COPY failed for table "actual_productions": ERROR: insert or update on table "actual_productions" violates foreign key constraint "fk_rails_473c650b37" DETAIL: Key (production_id)=(1) is not present in table "productions". pg_restore: executing SEQUENCE SET actual_productions_id_seq pg_restore: processing data for table "actual_sheets" pg_restore: [archiver (db)] Error from TOC entry 3410; 0 50944 TABLE DATA actual_sheets allen pg_restore: [archiver (db)] COPY failed for table "actual_sheets": ERROR: insert or update on table "actual_sheets" violates foreign key constraint "fk_rails_33f075fa1e" DETAIL: Key (sheet_id)=(1) is not present in table "sheets". pg_restore: executing SEQUENCE SET actual_sheets_id_seq pg_restore: processing data for table "affiliation_types"

so scrapped that db and started again; drop, create and migrate, then

pg_restore -v -d libval_development -c -C ~/Documents/20151110_2.dump > ~/Documents/20151110_c_C.log 2>&1

seems to be working because it seems to have familiar log, including hanging at (around 10:12pm):

pg_restore: processing data for table "***_rankings"

UPDATE: 11/11/2015 10:34AM

messing around with the pg_dump options, including taking out --clean and --create, or using --data-only, but no success. used

pg_restore -v -d libval_development

with no options, but it's hanging on ****_rankings again

UPDATE 11:00: so it seems to be working. it seems the data for that table really takes 30 minutes or more. i know this also because i started a pg_dump separately on the imac and it also hung on the same table, and took around 20 minutes. it seems, if you specify the options on pg_dump, then your pg_restore doesn't need any of those options.

UPdATE: 11:55: that didnt work.

also from the log files, realizing that db:drop and db:create obviate the need for -c and -C options in pg_restore. these commands fail; but then the database connects to the named db anyway, as in:

pg_restore: connecting to database for restore pg_restore: dropping DATABASE libval_development pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3586; 1262 50803 DATABASE libval_development allen pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop the currently open database Command was: DROP DATABASE libval_development; pg_restore: creating DATABASE libval_development pg_restore: [archiver (db)] could not execute query: ERROR: database "libval_development" already exists Command was: CREATE DATABASE libval_development WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.... pg_restore: connecting to new database "libval_development" pg_restore: creating SCHEMA public pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public allen pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public;

so i now tried the following: rake db:drop; rake db:create. BUT DO NOT migrate. the theory is that the pg_restore, with clean and create, will create the proper associations. but if this works, what then do i do with the migrations?

i had changed the migration order on my db, but had not manually changed the schema_migration table to reflect that. i dumped --clean into a file, and i tried to restore on my new machine using that dump file i got errors. i switched it up by doing the following: with corrected schema_migrations table pg_dump -C -c rake db:drop rake db:create pg_restore --verbose -c -C --no-acl --no-owner -h localhost -d libval_development ~/Documents/20150809_schema_upd_clean.dump > ~/Documents/20150809_schema_upd_clean.txt 2>&1 then i followed up with the following heroku pg:backups restore '[link]' GREEN which failed on a large table with 60mm rows keep getting this error: LOG: could not receive data from client: Connection reset by peer heroku docs says the following:

Although this log is emitted from postgres, the cause for the error has nothing to do with the database itself. Your application happened to crash while connected to postgres, and did not clean up its connection to the database. Postgres noticed that the client (your application) disappeared without ending the connection properly, and logged a message saying so. If you are not seeing your application’s backtrace, you may need to ensure that you are, in fact, logging to stdout (instead of a file) and that you have stdout sync’d.

https://devcenter.heroku.com/articles/postgres-logs-errors#log-unexpected-eof-on-client-connection

addressed here in gem which i don't have https://github.com/resque/resque/issues/367

http://stackoverflow.com/questions/13324352/heroku-postgresql-connection-reset-by-peer

this didn't happen in the last dump months ago, with same data. only thing i changed: upgraded to ruby 2.2, with a bunch of new gems.

postgres.conf file?

blame unicorns?

UPDATE trying pg:push; at 11:28am start processing data for large table rankings

UPDATE pg:backups restore fails at the 4 minute mark, when it hits the rankings table

started pg:push at 12:48, this time going to wait a longer time, because i see that the db size going beyond 4gb (despite a dump size of 1.24gb) is typical; also that the size as represented in the heroku web dashboard may not be completely representative.

i had done a shit load of changes to my db schema using migrations; but in order to keep things clean i had gone back and forth, changing existing migrations; i also put the creation of views and materialized views; in short my migration files were a mess after 18 months.

so first pg_dump of latest data `-a` (data-only) option;

created a new empty migration

copy pasted all the tables in my latest schema.rb file into a `def change` method in the new migration; 

then i erased all the other migration files except ones creating views and materialized views.

rake db:drop, db:create, db:migrate

pg_restore

the problem is that restore is used for replacing all data; which means i need to delete all existing records

this psql function doesn’t work: https://feeding.cloud.geek.nz/posts/restoring-single-table-from-postgres/

this isn’t the case for me: http://hcmc.uvic.ca/blogs/index.php?blog=22&p=8105&more=1&c=1&tb=1&pb=1

this might work: http://stackoverflow.com/a/21404595/1964078

keep getting this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2810; 0 34505 TABLE DATA titles allen pg_restore: [archiver (db)] COPY failed for table "titles": ERROR: duplicate key value violates unique constraint "titles_pkey"

this is good explanation: http://www.postgresql.org/message-id/[email protected]

after using -c:

pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2688; 2606 34513 CONSTRAINT titles_pkey allen pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop constraint titles_pkey on table titles because other objects depend on it DETAIL: constraint fk_rails_1cb48ffc71 on table companies_people_titles depends on index titles_pkey HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: ALTER TABLE ONLY public.titles DROP CONSTRAINT titles_pkey;

maybe because of this: http://dba.stackexchange.com/questions/29476/error-restoring-postgres-db

i think what is happening is that too many dependencies in db to just replace all records of a table; that means large updates to the database mean i need to restore the entire database; this is concerning because my migrations directory is getting out of hand; at a certain point i will need to create dbs from the schema rather than migration files.

UPDATE tried it with clean for entire db and didn't work. lots of unique key errors duplication.

trying with clean and create

http://www.postgresql.org/message-id/[email protected]

that didn't work.

so then i tried

db:drop db:create db:load:schema pg_restore pg_restore -v -d libval_development -c -C ~/Documents/20150707_all_c_C.dump

which is supposed to do a create and clean; but since I had already created db and schema, it kept dropping and then creating the tables, which led to errors.

so then I

pg_dump -Fc --verbose --no-acl --no-owner -a -h localhost -p 5432 libval_development > ~/Documents/20150707_data_only.dump

the -a means data only.

then I:

db:drop db:create db:migrate

I thought the migration would be very inefficient as compared to the db:schema:load, but actually it was instantaneous. then:

pg_restore -v -d libval_development -a ~/Documents/20150707_data_only.dump > ~/Documents/20150707_pg_restore_log.txt 2>&1

http://www.postgresql.org/message-id/[email protected]

it's slow but it seems to be working without error.

the whale_rankings always takes some time because of the 61mm records.

the restore seems to have worked; the new data is now in my db; but now the materialized_views are not working.

get this error: