Re: Second message: "Problem generating database using pgAdmin III - PostgreSQL version is 9.1"

On Tue, Sep 3, 2013 at 7:14 PM, Hager, Paul CIV NAVSEA Crane, NAVSEA
<paul(dot)hager(at)navy(dot)mil> wrote:
> I sent message with this title once and got nothing indicating it was received. I have, however, gotten exchanges dealing with 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 Windows machine that will convert them to Access. Someone else managed this but is on extended medical leave, leaving sketchy directions 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, since the 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 extended medical 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 make the tables. I don't know how this can happen if all of the "CREATE TABLES" have been edited out, but that's what the directions 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 files seemed 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 list has had to do that. I don't work with databases - I just need a simple cookbook/recipe. Once I have a procedure, I'll be 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