Pg_dump -column-inserts -h HNAME -p PNUMBER -d DBNAME -U UNAME > db_backup.sql Pg_dumpall -column-inserts -h HNAME -p PNUMBER -U UNAME > cluster_backup.sql Pg_dump -inserts -h HNAME -p PNUMBER -d DBNAME -U UNAME > db_backup.sql pg_dumpall -inserts -h HNAME -p PNUMBER -U UNAME > cluster_backup.sql This is slower ( INSERT needs to pass by the parser, planner, etc.), but will work regardless of whether your tables already contain some of the data in them. In this an INSERT sql query is created for each entry, instead of a single COPY command with all the entries. COPY public.enduser (id, name) FROM stdin ĬOPY public.enduser (id, name) FROM stdin Ī practical solution is to use either of the -inserts or -column-inserts flags with pg_dumpall or pg_dump. If you manually edit the backup sql file and duplicate the COPY command, along with the delimeter for the input stream, for each entry it will succeed despite some entries already existing in the table. A failure occurs if a given entry already exists in the given table. If any of the entries fed to the single COPY command fails, all of them fail. The COPY command is set to read from stdin with all the data being written to sdtin in one go. Pg_dump -h HNAME -p PNUMBER -d DBNAME -U UNAME > db_backup.sql pg_dumpall -h HNAME -p PNUMBER -U UNAME > cluster_backup.sql When creating a backup with pg_dumpall or pg_dump it will create a plain sql backup and use the COPY command to inject the data into the relevant tables. Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql How exactly (what parameters) was the backup taken and how exactly are you restoring? How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created? channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.Ĭaveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem. Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. An example: ERROR: insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"ĭETAIL: Key (channel_id)=(1) is not present in table "channels". In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored.
0 Comments
Leave a Reply. |