0

I am trying to import DB from DEV to Staging. I am not trying the pg_dump method as I don't know that and also when I tried to click on pg_utlity it ask for a password and when I type my password and enter its just going nothing happening. SO I tried the manual method of creating the DB,Schema.

My issue is I already have datas in DEV and I am importing tables via import method (right click on table>>import), the sequence is going wrong.

That is in new Db table when we insert it is staring from 1. it will cause mapping issues in application. I tried to change the current value of sequence , but it is bit difficult as it is taking too much time to do for all tables. Is there any way to solve this problem ?

Thanks

Rose

Mayur Prajapati
  • 5,454
  • 7
  • 41
  • 70
Rose
  • 101
  • 1
  • 2
  • 9

1 Answers1

0

Reason: The Sequence 'issue' that in the new database, is probably because the GUI tool used to copy Data over, is simply copying the Schema over to the new Database (which resets the Sequence state). This causes the new Database Sequence to start counting from 1 (instead of what it currently is, in the Old database).

Solutions:

  1. Using PG_DUMP isn't that difficult. If you have access to a command-line, and feel comfortable using it, many StackOverflow answers (like this one) should get such a Database copying job done within a few minutes (depending on the DB size) and with all the Sequences taken care of automatically. I would really recommend reading / understanding and using this method since its proven, and best advised in such a scenario.

  2. Paid Application: Migration Toolkit: Paid + Easy + GUI. Not something that I would recommend for everyone, but it probably may be an easy way out, if you're only used to the GUI and are not comfortable using the (free) PG_DUMP method given above.

Community
  • 1
  • 1
Robins Tharakan
  • 2,209
  • 19
  • 17
  • I tried pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434, but it is asking for password. I donot have password for DB. how to handle this? – Rose Aug 17 '15 at 11:02
  • There are a few answers on StackOverflow (for e.g. http://goo.gl/8jurxE) but I am assuming you are not interested in those. You can set the password for myuser94 by logging into the target database as postgres user and change your password like this (http://goo.gl/SmIBNf) .... If you are not the admin on this database ask your DBA to do the same for you. – Robins Tharakan Aug 17 '15 at 11:50
  • I could back up my test DB which is in my local host with pg_dump. In cmd prompt it says "PostgreSQL database dump complete". But where I can find the file. I did not gave any file name or any path. Can you please help – Rose Aug 20 '15 at 14:07
  • Where is the 'default' file location for the Dump depends on the application. I would start searching from My Documents / or / C:/Program Files / EnterpriseDB ./.. /.. / etc. Once you find that file, you probably have done only half the job. A 'Dump' needs to be followed by a 'Restore', which probably is pending and should ensure Schema + Data + Sequences are intact in the new (restored) Database. – Robins Tharakan Aug 21 '15 at 01:21
  • Hi Robin,I gave pg_dump -U postgres mydb and it is showing dump is complete. I have not given any file name or where to store. I checked in pgAdmin folders for the dump and my documents. but its not there. Do you know what would be the probable name of the file, so that I can search with that – Rose Aug 21 '15 at 05:45
  • Hi Robin,I gave pg_dump -U postgres mydb and it is showing dump is complete. I have not given any file name or where to store. I checked in pgAdmin folders for the dump and my documents. but its not there. Do you know what would be the probable name of the file, so that I can search with that – Rose Aug 21 '15 at 05:46