Transfer db from one port to another

Hello

Why don’t you simply change the port in postgresql.conf and restart the server?

Bye

Charles

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 09:58
To: pgsql-general <pgsql-general@postgresql.org>
Subject: [GENERAL] Transfer db from one port to another

I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and postgreSQL 9.4 on port 5532 with the latter set up to use with Bitnami stack to test php files I am generating from my db.

I want to transfer my db with three schemas from port 5432 to port 5532 to use within the bitnami stack. I have used pgAdmin to create a backup.sql and when using pgAdmin to restore the .sql to port 5532 I get the following error

ERROR: syntax error at or near "\"
LINE 26: \connect irll_project

with the lines in the .sql file created being

ALTER DATABASE irll_project OWNER TO postgres;

\connect irll_project

I have tried to use the Windows psql shell command to import using

\i 'C:/all.sql'

on pressing enter I see multiple occurrences saying invalid command, and then it ends with

error: out of memory detail: failed on request of size 268435456

What is the correct command line?

Killian DriscoIl

On 23 December 2015 at 10:05, Charles Clavadetscher <
clavadetscher@swisspug.org> wrote:

Hello

Why don’t you simply change the port in postgresql.conf and restart the
server?

I am attempting to learn to use the dump and restore, so would like to
figure out how to do it via an export and import.

Killian Driscoll <killiandriscoll@gmail.com> wrote:

I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and postgreSQL
9.4 on port 5532 with the latter set up to use with Bitnami stack to test php
files I am generating from my db.

I want to transfer my db with three schemas from port 5432 to port 5532 to use
within the bitnami stack. I have used pgAdmin to create a backup.sql and when
using pgAdmin to restore the .sql to port 5532 I get the following error

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

(untestet, please read *before* the documentation)

I think, this should work. No idea what's wrong with pgAdmin, not using
that.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Charles Clavadetscher <clavadetscher@swisspug.org> wrote:

Hello

Why don’t you simply change the port in postgresql.conf and restart the server?

i think, he wants the data from the one database within the other,
because of that he can't change the port.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 23 December 2015 at 10:29, Andreas Kretschmer <akretschmer@spamfence.net>
wrote:

Killian Driscoll <killiandriscoll@gmail.com> wrote:

I am using Windows 8 64 bit, with postgreSQL 9.3 on port 5432 and

postgreSQL

9.4 on port 5532 with the latter set up to use with Bitnami stack to

test php

files I am generating from my db.

I want to transfer my db with three schemas from port 5432 to port 5532

to use

within the bitnami stack. I have used pgAdmin to create a backup.sql and

when

using pgAdmin to restore the .sql to port 5532 I get the following error

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands.
Attached is a screenshot of the shell window - what am I doing wrong?

Hello Killian

I want to transfer my db with three schemas from port 5432 to port 5532 to use
within the bitnami stack. I have used pgAdmin to create a backup.sql and when
using pgAdmin to restore the .sql to port 5532 I get the following error

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands. Attached is a screenshot of the shell window - what am I doing wrong?

This should be done from an OS shell, not from psql.

Bye
Charles

(untestet, please read *before* the documentation)

I think, this should work. No idea what's wrong with pgAdmin, not using
that.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 23 December 2015 at 10:58, Charles Clavadetscher <
clavadetscher@swisspug.org> wrote:

Hello Killian

I want to transfer my db with three schemas from port 5432 to port 5532

to use

within the bitnami stack. I have used pgAdmin to create a backup.sql

and when

using pgAdmin to restore the .sql to port 5532 I get the following error

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands.

Attached is a screenshot of the shell window - what am I doing wrong?

This should be done from an OS shell, not from psql.

Do you mean Windows command prompt?

From: Killian Driscoll [mailto:killiandriscoll@gmail.com]
Sent: Mittwoch, 23. Dezember 2015 11:02
To: Charles Clavadetscher <clavadetscher@swisspug.org>
Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Transfer db from one port to another

On 23 December 2015 at 10:58, Charles Clavadetscher <clavadetscher@swisspug.org <mailto:clavadetscher@swisspug.org> > wrote:

Hello Killian

I want to transfer my db with three schemas from port 5432 to port 5532 to use
within the bitnami stack. I have used pgAdmin to create a backup.sql and when
using pgAdmin to restore the .sql to port 5532 I get the following error

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands. Attached is a screenshot of the shell window - what am I doing wrong?

This should be done from an OS shell, not from psql.

Do you mean Windows command prompt?

Yes

Bye
Charles

(untestet, please read *before* the documentation)

I think, this should work. No idea what's wrong with pgAdmin, not using
that.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org> )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the
commands. Attached is a screenshot of the shell window - what am I
doing wrong?

those are system shell commands, not psql sql commands. catch-22, in the
windows environment, postgresql's command tools probably aren't in the
path, so to execute the above commands try this...

start -> run -> *CMD* <enter>

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName>***path "c:\Program Files\PostgreSQL\9.4\bin";%path%*
C:\Users\YourName>*pg_dump -Fc -p 5432 */<dbname>/*| pg_restore -p
5532*

if your postgres is installed somewhere else, replace "c:\Program
Files\PostgreSQL\9.4\bin" in the PATH command with its actual location
\bin ....

--
john r pierce, recycling bits in santa cruz

On 23 December 2015 at 11:07, John R Pierce <pierce@hogranch.com> wrote:

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands.
Attached is a screenshot of the shell window - what am I doing wrong?

those are system shell commands, not psql sql commands. catch-22, in
the windows environment, postgresql's command tools probably aren't in the
path, so to execute the above commands try this...

start -> run -> *CMD* <enter>

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName> *path "c:\Program Files\PostgreSQL\9.4\bin";%path%*
C:\Users\YourName>* pg_dump -Fc -p 5432 **<dbname>** | pg_restore -p
5532*

Thanks. When I do this I get an error: could not find a "pg_dump" to
execute - I've used the path *"C:\Program
Files\PostgreSQL\9.3\bin";%path% *which
appears to be correct

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 11:19
To: John R Pierce <pierce@hogranch.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Transfer db from one port to another

On 23 December 2015 at 11:07, John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com> > wrote:

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands. Attached is a screenshot of the shell window - what am I doing wrong?

those are system shell commands, not psql sql commands. catch-22, in the windows environment, postgresql's command tools probably aren't in the path, so to execute the above commands try this...

start -> run -> CMD <enter>

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName> path "c:\Program Files\PostgreSQL\9.4\bin";%path%
C:\Users\YourName> pg_dump -Fc -p 5432 <dbname> | pg_restore -p 5532

Thanks. When I do this I get an error: could not find a "pg_dump" to execute - I've used the path "C:\Program Files\PostgreSQL\9.3\bin";%path% which appears to be correct

You may try calling the app without setting the path first or check the location browsing the file system:

C:\Program Files\PostgreSQL\9.3\bin\ pg_dump -Fc -p 5432 <dbname>

Don’t forget to replace <dbname> with the database that you want to dump.

if your postgres is installed somewhere else, replace "c:\Program Files\PostgreSQL\9.4\bin" in the PATH command with its actual location \bin ....

--
john r pierce, recycling bits in santa cruz

On 23 December 2015 at 11:19, Killian Driscoll <killiandriscoll@gmail.com>
wrote:

On 23 December 2015 at 11:07, John R Pierce <pierce@hogranch.com> wrote:

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands.
Attached is a screenshot of the shell window - what am I doing wrong?

those are system shell commands, not psql sql commands. catch-22, in
the windows environment, postgresql's command tools probably aren't in the
path, so to execute the above commands try this...

start -> run -> *CMD* <enter>

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName> *path "c:\Program
Files\PostgreSQL\9.4\bin";%path%*
C:\Users\YourName>* pg_dump -Fc -p 5432 **<dbname>** | pg_restore -p
5532*

Thanks. When I do this I get an error: could not find a "pg_dump" to
execute - I've used the path *"C:\Program
Files\PostgreSQL\9.3\bin";%path% *which appears to be correct

Sorry, forgot to add: once I get the warning that the Pg_dump can't be
found there is then a password prompt; I tried the db password and the pc
password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project" failed:
FATAL: p
assword authentication failed for user "killian"
pg_restore: [archiver] input file is too short (read 0, expected 5)

On 12/23/2015 2:19 AM, Killian Driscoll wrote:

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName>***path "c:\Program
Files\PostgreSQL\9.4\bin";%path%*
C:\Users\YourName>*pg_dump -Fc -p 5432 */<dbname>/*|
pg_restore -p 5532*

Thanks. When I do this I get an error: could not find a "pg_dump" to
execute - I've used the path *"C:\Program
Files\PostgreSQL\9.3\bin";%path% *which appears to be correct

to test this, try (at that same command prompt)

*dir **"C:\Program Files\PostgreSQL\9.3\bin"
*

that should list the postgres utilities and stuff, including psql.exe,
pg_dump.exe, etc.

like, on a system here (which uses a non-default path), its ...

C:\Users\xxxx>dir "d:\PostgreSQL\9.3\bin"
Volume in drive D is Drive_Dee
Volume Serial Number is ****

Directory of d:\PostgreSQL\9.3\bin

09/30/2014 12:28 AM <DIR> .
09/30/2014 12:28 AM <DIR> ..
07/21/2014 11:51 PM 69,120 clusterdb.exe
...
07/21/2014 11:51 PM 384,000 pg_dump.exe
...
07/21/2014 11:51 PM 180,736 pg_restore.exe
...
07/21/2014 11:51 PM 408,576 psql.exe
...
02/05/2014 01:33 AM 77,824 zlib1.dll
70 File(s) 56,177,637 bytes
3 Dir(s) 1,687,179,091,968 bytes free

C:\Users\xxxx>

(note I trimmed about 65 files)

--
john r pierce, recycling bits in santa cruz

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Killian Driscoll
Sent: Mittwoch, 23. Dezember 2015 11:26
To: John R Pierce <pierce@hogranch.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Transfer db from one port to another

On 23 December 2015 at 11:19, Killian Driscoll <killiandriscoll@gmail.com <mailto:killiandriscoll@gmail.com> > wrote:

On 23 December 2015 at 11:07, John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com> > wrote:

On 12/23/2015 1:40 AM, Killian Driscoll wrote:

Try it with plain pg_dump.

pg_dump -h localhost -p 5432 -Fc <dbname> > dump.sql

pg_restore -h localhost -p 5532 dump.sql

I tried this, but nothing appears to happen when entering the commands. Attached is a screenshot of the shell window - what am I doing wrong?

those are system shell commands, not psql sql commands. catch-22, in the windows environment, postgresql's command tools probably aren't in the path, so to execute the above commands try this...

start -> run -> CMD <enter>

(or, click on an 'Command Prompt' shortcut).

C:\Users\YourName> path "c:\Program Files\PostgreSQL\9.4\bin";%path%
C:\Users\YourName> pg_dump -Fc -p 5432 <dbname> | pg_restore -p 5532

Thanks. When I do this I get an error: could not find a "pg_dump" to execute - I've used the path "C:\Program Files\PostgreSQL\9.3\bin";%path% which appears to be correct

Sorry, forgot to add: once I get the warning that the Pg_dump can't be found there is then a password prompt; I tried the db password and the pc password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project" failed: FATAL: p
assword authentication failed for user "killian"

Do you have a user killian in the database? If not you can either create it or use pg_dump with the –U switch to set it to the existing user (and with privileges on the database of course).

pg_restore: [archiver] input file is too short (read 0, expected 5)

if your postgres is installed somewhere else, replace "c:\Program Files\PostgreSQL\9.4\bin" in the PATH command with its actual location \bin ....

--
john r pierce, recycling bits in santa cruz

On 12/23/2015 2:25 AM, Killian Driscoll wrote:

Sorry, forgot to add: once I get the warning that the Pg_dump can't be
found there is then a password prompt; I tried the db password and the
pc password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project" failed:
FATAL: p
assword authentication failed for user "killian"

note that databases don't have passwords, database USERS have
passwords. 'killian' probably doesn't have a database user, and since
you didn't specify a user, it defaulted to your system username
(expecting that user to have been created in postgres, and wanting that
probably non-existant postgres users passsword)

so, ok, try the command with -U postgres, as *
*

*pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore -U
postgres -p 5532*

if/when it prompts for a password, thats the password of the 'postgres'
database user, as configured in the postgres servers.

note it will prompt for the password a couple times, once for postgres
on port 5432, and again for postgres on port 5532, at least if both
database services are configured to require passwords for local connections.

--
john r pierce, recycling bits in santa cruz

On 23 December 2015 at 11:36, John R Pierce <pierce@hogranch.com> wrote:

On 12/23/2015 2:25 AM, Killian Driscoll wrote:

Sorry, forgot to add: once I get the warning that the Pg_dump can't be
found there is then a password prompt; I tried the db password and the pc
password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project" failed:
FATAL: p
assword authentication failed for user "killian"

note that databases don't have passwords, database USERS have passwords.
'killian' probably doesn't have a database user, and since you didn't
specify a user, it defaulted to your system username (expecting that user
to have been created in postgres, and wanting that probably non-existant
postgres users passsword)

so, ok, try the command with -U postgres, as

*pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore -U
postgres -p 5532*

OK - I did the dir and it shows that the dump and restore.exe are there,

but running the above gives the below errors

09/06/2014 08:35 381,952 pg_dump.exe

09/06/2014 08:35 180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project | pg_restore
-U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file: Invalid argument

On 12/23/2015 03:43 AM, Killian Driscoll wrote:

On 23 December 2015 at 11:36, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.com>> wrote:

On 12/23/2015 2:25 AM, Killian Driscoll wrote:

Sorry, forgot to add: once I get the warning that the Pg_dump
can't be found there is then a password prompt; I tried the db
password and the pc password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project"
failed: FATAL: p
assword authentication failed for user "killian"

note that databases don't have passwords, database USERS have
passwords. 'killian' probably doesn't have a database user, and
since you didn't specify a user, it defaulted to your system
username (expecting that user to have been created in postgres, and
wanting that probably non-existant postgres users passsword)

so, ok, try the command with -U postgres, as *
*

*pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore
-U postgres -p 5532*

OK - I did the dir and it shows that the dump and restore.exe are there,
but running the above gives the below errors

09/06/2014 08:35 381,952 pg_dump.exe

09/06/2014 08:35 180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
pg_restore -U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file: Invalid argument

Try breaking the above down into two steps:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

pg_restore -U postgres -p 5532 irll_project.out

if/when it prompts for a password, thats the password of the
'postgres' database user, as configured in the postgres servers.

note it will prompt for the password a couple times, once for
postgres on port 5432, and again for postgres on port 5532, at least
if both database services are configured to require passwords for
local connections.

--
john r pierce, recycling bits in santa cruz

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 23 December 2015 at 14:56, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 12/23/2015 03:43 AM, Killian Driscoll wrote:

On 23 December 2015 at 11:36, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.com>> wrote:

On 12/23/2015 2:25 AM, Killian Driscoll wrote:

Sorry, forgot to add: once I get the warning that the Pg_dump
can't be found there is then a password prompt; I tried the db
password and the pc password but both fail:

Password:
pg_dump: [archiver (db)] connection to database "irll_project"
failed: FATAL: p
assword authentication failed for user "killian"

note that databases don't have passwords, database USERS have
passwords. 'killian' probably doesn't have a database user, and
since you didn't specify a user, it defaulted to your system
username (expecting that user to have been created in postgres, and
wanting that probably non-existant postgres users passsword)

so, ok, try the command with -U postgres, as *
*

*pg_dump -Fc -p 5432 **-U postgres **irll_project | pg_restore
-U postgres -p 5532*

OK - I did the dir and it shows that the dump and restore.exe are there,
but running the above gives the below errors

09/06/2014 08:35 381,952 pg_dump.exe

09/06/2014 08:35 180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project |
pg_restore -U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to
execute

pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] could not write to output file: Invalid
argument

Try breaking the above down into two steps:

pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project

Doing this step I get response
could not find a "pg_dump" to execute

On 23 Dec 2015, at 12:43, Killian Driscoll <killiandriscoll@gmail.com> wrote:

OK - I did the dir and it shows that the dump and restore.exe are there, but running the above gives the below errors

09/06/2014 08:35 381,952 pg_dump.exe

09/06/2014 08:35 180,224 pg_restore.exe

C:\Users\killian>path "C:\Program Files\PostgreSQL\9.3\bin";%path%

C:\Users\killian>pg_dump -Fc -p 5432 -U postgres irll_project | pg_restore -U po
stgres -p 5532
ccoulould not findd a n "pg_restore" to executeot find a "pg_dump" to execute

Aren't you trying to move a database to PG 9.4? Then you need to use the pg_dump and pg_restore utilities of the 9.4 installation, not those of the 9.3 one. Those utilities are guaranteed to be backwards compatible, but they're not necessarily forwards compatible.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general