I am trying to copy my database from one server to another (postgres10). I have tried using the interface of pgadmin4 to take a backup, create empty database on the new server and use the interface restore option. This is the first time I have tried to move to a new server so I am not very experienced. The data got copied to the new server but the sequence was not, so when I try to insert a new item to the database the not null restriction makes it invalid.

Instead I tried to use the command line to backup and restore the database with these commands.

pg_dump -U postgres -Fc my_db > my_db.db

pg_restore -h <ip remote server> -p <port remote server> -U postgres -C my_db.db

While this worked to copy the data over, the issue still persist with sequence. I have seen similar posts about this issue but not found anyone who has actually solved the problem. Is this a difficult task to complete? What I want is to copy by database over just as it is to another server.

| improve this question | | | | |
  • What do you mean with sequence? What exact INSERT statement did you try and what error did you get? – ypercubeᵀᴹ Apr 29 '18 at 20:35
  • The tables was initially created with serial not null and this created the sequence and nextval() function automatically for incrementing id's. Now if i run an insert SQL (from my application) i get "not null value in column "test" violates not-null constraint. The table created on the new server only has "id integer not null". – geogrow Apr 29 '18 at 20:41
  • 1
    So the sequences did not survive the backup and restore? Which version of Postgres 10 are you using? (run select version(); on both servers) – ypercubeᵀᴹ Apr 29 '18 at 20:48
  • No the sequences got lost during the migration. On my original server its 10.1. The server I try to copy it to (on my raspberry pi for development training) its 9.6.7. – geogrow Apr 29 '18 at 20:58
  • 1
    Right, I tried it (dump from 10, restore to 9.6) and got errors during the restore. Errors regarding sequences. You probbaly got them, too but didn't notice. If you had rrun the restore with the -e option (exit on errors), it would have failed. – ypercubeᵀᴹ Apr 29 '18 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.