Menu
I made a backup of a database on my mac and tried to restore it on a computer with ubuntu. When I execute
psql -U uname -d dbname -f ~/dump_from_mac
I have a lot of error messages like "invalid command \N" and "relation 'SomeTable' does not exist". My question is very similar with Can't copy table to another database with pg_dump but I don't know how to fix my dump file. I wipe my mac and can't make new dump.
771
Restoring a text dump file is only possible using psql. This is an interactive interface for managing the PostgreSQL database. So, for restoring the text dump we can take the psql console and run the following command. psql -U db_user db_name < dump_file.sql
psql has an inbuilt mode ‘ stop on the first error ‘. So, we will switch psql to ‘ stop on the first error mode’. This displays the initial trigger for the error. To switch the mode, we will use the following command: This command gives the actual reason for the error which will help us to fix it. 2. Improper restoring of the dump file
In order to use pg_restore, we must convert the text file to tar format and then execute the restore command. So, to fix the error, we will restore the dump file in the appropriate format. [Still, having trouble in restoring a text dump using psql?
Here we will use pg_dump to export a PostgreSQL database. First, access the database directly or remotely to dump it. Now, dump the database using the following command. pg_dump -U db_user -W -F p db_name > /path_of_the_dump/dump_file.sql Here, the db_user is the database user and db_name is the database name. The other options are,
My problem was solved by setting postgresql-contrib package
sudo apt-get install postgresql-contrib
and creating extension uuid-ossp in my db
CREATE EXTENSION "uuid-ossp";
My db haven't this extension by default and psql could not execute uuid_generate_v1() function from my dump file. In most cases install postgresql-contrib is enough, but sometimes problem may be in some missed extensions too.
80
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With