var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: restore only data to an existing schema
I want to restore only data to my database[contains only schema] from dump.sql.
i tried this command "psql -U postgres -h localhost -a mydb < dump.sql".
but it is throwing error for all existing table in my database.
ALTER TABLE ONLY users
ADD CONSTRAINT fkfc6ecb094a74de9a FOREIGN KEY (address_fk) REFERENCES address_users(id);
ERROR: constraint "fkfc6ecb094a74de9a" for relation "users" already exists
Could you please help me on this?
Thanks in advance.
You need to create the dump using pg_dump --data-only
I tried this. Now the problem is, "users table has reference in visa_details". so it is trying to insert record with reference userid 3361. users table is empty now. so i am getting error like
COPY visa_details (id, country, visa_till_date, visa_type, userid) FROM stdin;
ERROR: insert or update on table "visa_details" violates foreign key constraint "fk72040444c2059795"
DETAIL: Key (userid)=(3361) is not present in table "users".
Are you sure the source database has the same foreign key constraints as the target database? pg_dump writes the data in the correct order so it should work.
You can verify if the order of the tables is correct by looking at the dump file and check if the users table is listed before the visa_details table.
If the order in the dump file is indeed wrong then the only workaround would be to disable all constraints and re-enable them after the import
yes source database has the same foreign key as target database.
pg_dump writes the data in ascending order[table name].
How do we disable all constraints and re-enables after import?
Originally Posted by
I forgot that this is a limitation of the --data-only parameter (I just tried it and saw the warning pg_dump shows on the command line. Error messages are useful)
Why don't you just use the --clean parameter?
That will drop all objects that exist in the target and the import everything. Most probably the easiest solution if you want an exact copy of the source
In my case --clean wont help me.
scenario : some time we change the database structure in entity level. Those changes will be updated when we start app server. Then we need to import only data.
step 1: i have to take dump. it can be with schema or with out.
step 2: drop database.
step 3: when start my app server, it will create database schema.
step 4: i need to import only data.
I remember, long back i used extra parameter in psql command itself to ignore constrains. Now i forgot that parameter name.
So i need to disable the constraints when import data.
If the schema of the target is different to the schema of the database where you took the dump, how can you import the data?
To "disable" constraints you need to drop them.
target schema have more columns than source scheme. we are not going to change existing columns rather than add more columns
About how many tables are we talking about?
What you could do is to use the native format. Before importing create a listing file (using pg_restore --list). In the listing file you can remove the table creation and re-order the tables according to their dependencies.
That part can probably be automated using commandline tools like grep and sed