Re: Second message: "Problem generating database using pgAdmin III - PostgreSQL version is 9.1" - Mailing list pgadmin-support
From | Dave Page |
---|---|
Subject | Re: Second message: "Problem generating database using pgAdmin III - PostgreSQL version is 9.1" |
Date | |
Msg-id | CA+OCxoxJ3o6-6bkUp68KR1qa=d2XH1-4PRnvMdJSoY1dv6CYMg@mail.gmail.com Whole thread Raw |
In response to | Second message: "Problem generating database using pgAdmin III - PostgreSQL version is 9.1" ("Hager, Paul CIV NAVSEA Crane, NAVSEA" <paul.hager@navy.mil>) |
List | pgadmin-support |
On Tue, Sep 3, 2013 at 7:14 PM, Hager, Paul CIV NAVSEA Crane, NAVSEA <paul.hager@navy.mil> wrote: > I sent message with this title once and got nothing indicating it was received. I have, however, gotten exchanges dealingwith other rather arcane problems. At a minimum, could I get some acknowledgment that my message has been received? > > I'll provide a brief recap: > I'm trying to port two PostgreSQL databases to Windows XP. Once I do that, I will run those through a tool in the Windowsmachine that will convert them to Access. Someone else managed this but is on extended medical leave, leaving sketchydirections behind that no one here has been able to decipher. Note: none of this is part of my job description -I'm filling in because no one else seems to know anything about how to do this. I'll focus on only ONE database, sincethe process is the same for both. Probably noone answered because your question is mostly off-topic for this list. > In brief. Do > pg_dump -s dfile-pgs -f dsYYYYMMDD > pg_dump -s dfile-pgs -f dALLYYYYMMDD > > The above worked (for me) Take these files and move to Windows XP. That's now done. > Now edit the files as follows: comment out "\connect" and all of the "GRANT INSERT" commands. Save the result as *.sql > Edit all the files containing tables and data. Remove the "CREATE TABLE" commands so just the "INSERT" commands are left. Save again. (NOTE: this applied to both files - both had "create table" commands.) > > I did all of the above, apparently successfully. > > This is just a recipe - none of it makes sense to me. However, these steps were followed by the person now on extendedmedical leave to provide input to the pgAdmin program. The next set of instructions involve pgAdmin: > > Select SQL from the toolbar and open file, selecting the "s" file (supposedly the schema file). This is supposed to makethe tables. I don't know how this can happen if all of the "CREATE TABLES" have been edited out, but that's what thedirections say. Both of those files should be identical - you've run pg_dump with the same options (-s dfile-pgs) each time, but just output it to a different file. You are correct though - no tables will be created if you're removed the CREATE TABLE statements. Supposedly one is to run "pgsql" file option. It doesn't exist anywhere. There is an option to run a macro, which I selected and it seemed to run. However, nothing was created. There was nothing I could see anywhere and nothing to save. The next step is to load the files that have the "insert" commands in them. I assume this would be the ALL files. I assume you mean "psql" not "pgsql". This is the command line interface to PostgreSQL. pgAdmin does have a plugin option to run it (if it's on your machine in a location that pgAdmin can find), but that will run it in interactive mode. Your instructions should almost certainly tell you to run it from the command line and feed it the files you're creating, e.g. c:\>Program Files\PostgreSQL\9.2\bin> psql -U <username> -h localhost <databasename> < file.sql > I never saw any "insert table" commands. There were other insert commands but it wasn't clear what they did, and the filesseemed awfully small to produce the databases. Needless to say, none of this worked for me. If you ran the commands above, there wouldn't be any INSERT statements as you did schema-only dumps in both cases. Even if you left off the -s, you still wouldn't see them, as pg_dump will default to using COPY for data as it's a lot faster. To get INSERT statements, you'd need to omit the -s flag and add "--inserts" or "--column-inserts". > Here's all I want. I want to move some hypothetical PostgreSQL database on Linux to Windows. Surely someone on this listhas had to do that. I don't work with databases - I just need a simple cookbook/recipe. Once I have a procedure, I'llbe turning it over to other people. Shouldn't be anything like as complex as your procedure above. If you just want to dump an entire database, and you have matching tablespaces and roles on the target server, on the Linux box do: $ pg_dump -U <username> -h host > file.sql (add appropriate options for port number etc. if you need to, and optionally add the -s option back if you just want the one schema) Move the file to the Windows box, and then do: C:\> psql -U <username> -h localhost <databasename> < file.sql Again, add any other non-standard options you may need like "-p <port>", and of course, ensure you've created the target database first. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
pgadmin-support by date: