Обсуждение: 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

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

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

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

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 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

On 12/23/2015 06:13 AM, Killian Driscoll wrote:

On 23 December 2015 at 14:56, Adrian Klaver <adrian.klaver@aklaver.com
<mailto: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>

        <mailto: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

So cd into:

C:\Program Files\PostgreSQL\9.3\bin

and try:

pg_dump --help

that will at least establish that the command is being found.

    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 <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/23/2015 06:35 AM, Killian Driscoll wrote:

On 23 December 2015 at 15:30, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

    On 12/23/2015 06:13 AM, Killian Driscoll wrote:

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

        <mailto: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>
                 <mailto:pierce@hogranch.com <mailto:pierce@hogranch.com>>
                 <mailto:pierce@hogranch.com
        <mailto:pierce@hogranch.com> <mailto: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


    So cd into:

    C:\Program Files\PostgreSQL\9.3\bin

    and try:

    pg_dump --help

    that will at least establish that the command is being found.


OK - --help on the 9.3 lists help options

In your original post you said you have a 9.3 instance and a 9.4 instance.

From your post I would say the 9.3 instance was installed by the one click installer from EDB and the 9.4 from Bitami, is that correct?

So do you know where the 9.4 binaries are installed?

             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 <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/23/2015 06:50 AM, Killian Driscoll wrote:

On 23 December 2015 at 15:47, Adrian Klaver <adrian.klaver@aklaver.com

             So cd into:

             C:\Program Files\PostgreSQL\9.3\bin

             and try:

             pg_dump --help

             that will at least establish that the command is being found.


        OK - --help on the 9.3 lists help options


    In your original post you said you have a 9.3 instance and a 9.4
    instance.

     >From your post I would say the 9.3 instance was installed by the
    one click installer from EDB and the 9.4 from Bitami, is that correct?

Correct.


    So do you know where the 9.4 binaries are installed?

If by binaries, you mean the program files they are installed
C:\Bitnami\wappstack-5.5.30-0\postgresql\bin

Per previous posts you want, whenever possible, to us a newer version of pg_dump to move a database from an older version(9.3) to a newer one(9.4). Therefore you should do your dump and restore using the pg_dump.exe and pg_restore.exe from the Bitanami bin directory. I would cd to the above directory and do:

pg_dump -V
pg_restore -V

to make sure the programs are found and are the 9.4 versions.

Then do:

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

pg_restore -U postgres -p 5532 irll_project.out

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On 23 December 2015 at 16:02, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

    On 12/23/2015 06:50 AM, Killian Driscoll wrote:

        On 23 December 2015 at 15:47, Adrian Klaver
        <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

                      So cd into:

                      C:\Program Files\PostgreSQL\9.3\bin

                      and try:

                      pg_dump --help

                      that will at least establish that the command is
        being found.


                 OK - --help on the 9.3 lists help options


             In your original post you said you have a 9.3 instance and
        a 9.4
             instance.

              >From your post I would say the 9.3 instance was installed
        by the
             one click installer from EDB and the 9.4 from Bitami, is
        that correct?

        Correct.


             So do you know where the 9.4 binaries are installed?

        If by binaries, you mean the program files they are installed
        C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


    Per previous posts you want, whenever possible, to us a newer
    version of pg_dump to move a database from an older version(9.3) to
    a newer one(9.4). Therefore you should do your dump and restore
    using the pg_dump.exe and pg_restore.exe from the Bitanami bin
    directory. I would cd to the above directory and do:

    pg_dump -V
    pg_restore -V

    to make sure the programs are found and are the 9.4 versions.

    Then do:

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

    pg_restore -U postgres -p 5532 irll_project.out


It appeared to work with this method, but it has now been running for
almost 4 hours with no result. The db is not that large (probably v.
small by most standards) and the .out file is ~200mb

What is running, the dump or the restore?

    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/23/2015 11:09 AM, Killian Driscoll wrote:

On 23 December 2015 at 20:07, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

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

        On 23 December 2015 at 16:02, Adrian Klaver
        <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>> wrote:

             On 12/23/2015 06:50 AM, Killian Driscoll wrote:

                 On 23 December 2015 at 15:47, Adrian Klaver
                 <adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com

        <mailto:adrian.klaver@aklaver.com>>




                               So cd into:

                               C:\Program Files\PostgreSQL\9.3\bin

                               and try:

                               pg_dump --help

                               that will at least establish that the
        command is
                 being found.


                          OK - --help on the 9.3 lists help options


                      In your original post you said you have a 9.3
        instance and
                 a 9.4
                      instance.

                       >From your post I would say the 9.3 instance was
        installed
                 by the
                      one click installer from EDB and the 9.4 from
        Bitami, is
                 that correct?

                 Correct.


                      So do you know where the 9.4 binaries are installed?

                 If by binaries, you mean the program files they are
        installed
                 C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


             Per previous posts you want, whenever possible, to us a newer
             version of pg_dump to move a database from an older
        version(9.3) to
             a newer one(9.4). Therefore you should do your dump and restore
             using the pg_dump.exe and pg_restore.exe from the Bitanami bin
             directory. I would cd to the above directory and do:

             pg_dump -V
             pg_restore -V

             to make sure the programs are found and are the 9.4 versions.

             Then do:

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

             pg_restore -U postgres -p 5532 irll_project.out


        It appeared to work with this method, but it has now been
        running for
        almost 4 hours with no result. The db is not that large (probably v.
        small by most standards) and the .out file is ~200mb


    What is running, the dump or the restore?

The restore - I can see the dump .out file that was created at 16hr in
the postgresql/bin folder

So how are you determining it is running and that it is not doing anything?

What does the Postgres log for the 9.4 instance show?

             --
             Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

It's possible the restore is still building indexes.

What does it show when you run this query?

SELECT datname,
       pid as pid,
       client_addr,
       usename as user,
       query,
       CASE WHEN waiting = TRUE
            THEN 'BLOCKED'
            ELSE 'no'
        END as waiting,
       query_start,
       current_timestamp - query_start as duration
  FROM pg_stat_activity
 WHERE pg_backend_pid() <> pid
ORDER BY datname,
         query_start;

On Wed, Dec 23, 2015 at 2:14 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 12/23/2015 11:09 AM, Killian Driscoll wrote:

On 23 December 2015 at 20:07, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

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

        On 23 December 2015 at 16:02, Adrian Klaver
        <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>> wrote:

             On 12/23/2015 06:50 AM, Killian Driscoll wrote:

                 On 23 December 2015 at 15:47, Adrian Klaver
                 <adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com

        <mailto:adrian.klaver@aklaver.com>>




                               So cd into:

                               C:\Program Files\PostgreSQL\9.3\bin

                               and try:

                               pg_dump --help

                               that will at least establish that the
        command is
                 being found.


                          OK - --help on the 9.3 lists help options


                      In your original post you said you have a 9.3
        instance and
                 a 9.4
                      instance.

                       >From your post I would say the 9.3 instance was
        installed
                 by the
                      one click installer from EDB and the 9.4 from
        Bitami, is
                 that correct?

                 Correct.


                      So do you know where the 9.4 binaries are installed?

                 If by binaries, you mean the program files they are
        installed
                 C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


             Per previous posts you want, whenever possible, to us a newer
             version of pg_dump to move a database from an older
        version(9.3) to
             a newer one(9.4). Therefore you should do your dump and restore
             using the pg_dump.exe and pg_restore.exe from the Bitanami bin
             directory. I would cd to the above directory and do:

             pg_dump -V
             pg_restore -V

             to make sure the programs are found and are the 9.4 versions.

             Then do:

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

             pg_restore -U postgres -p 5532 irll_project.out


        It appeared to work with this method, but it has now been
        running for
        almost 4 hours with no result. The db is not that large (probably v.
        small by most standards) and the .out file is ~200mb


    What is running, the dump or the restore?

The restore - I can see the dump .out file that was created at 16hr in
the postgresql/bin folder

So how are you determining it is running and that it is not doing anything?

What does the Postgres log for the 9.4 instance show?

             --
             Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
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

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On 12/23/2015 11:17 AM, Killian Driscoll wrote:

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

    So how are you determining it is running and that it is not doing
    anything?

Since I ran the restore the windows shell has been 'active' with codes
lines running - I took that as being active.....!

So what do they say?

    What does the Postgres log for the 9.4 instance show?

Where is the log - here Control Panel\System and Security\Administrative
Tools in event viewer or elsewhere?

My guess in the Bitnami directory tree under logs/ or something similar.

                      --
                      Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>>




             --
             Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Do not stop the active restore.
Just run psql from the command shell in the Bitnami binary directory and use -U postgres and -p 5532 flags.

On Wed, Dec 23, 2015 at 2:22 PM, Killian Driscoll <killiandriscoll@gmail.com> wrote:

On 23 December 2015 at 20:19, Melvin Davidson <melvin6925@gmail.com> wrote:

It's possible the restore is still building indexes.

What does it show when you run this query?

Where do I run this query? Do I stop the restore that is 'active'?

SELECT datname,
       pid as pid,
       client_addr,
       usename as user,
       query,
       CASE WHEN waiting = TRUE
            THEN 'BLOCKED'
            ELSE 'no'
        END as waiting,
       query_start,
       current_timestamp - query_start as duration
  FROM pg_stat_activity
 WHERE pg_backend_pid() <> pid
ORDER BY datname,
         query_start;

On Wed, Dec 23, 2015 at 2:14 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 12/23/2015 11:09 AM, Killian Driscoll wrote:

On 23 December 2015 at 20:07, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

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

        On 23 December 2015 at 16:02, Adrian Klaver
        <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>> wrote:

             On 12/23/2015 06:50 AM, Killian Driscoll wrote:

                 On 23 December 2015 at 15:47, Adrian Klaver
                 <adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com

        <mailto:adrian.klaver@aklaver.com>>




                               So cd into:

                               C:\Program Files\PostgreSQL\9.3\bin

                               and try:

                               pg_dump --help

                               that will at least establish that the
        command is
                 being found.


                          OK - --help on the 9.3 lists help options


                      In your original post you said you have a 9.3
        instance and
                 a 9.4
                      instance.

                       >From your post I would say the 9.3 instance was
        installed
                 by the
                      one click installer from EDB and the 9.4 from
        Bitami, is
                 that correct?

                 Correct.


                      So do you know where the 9.4 binaries are installed?

                 If by binaries, you mean the program files they are
        installed
                 C:\Bitnami\wappstack-5.5.30-0\postgresql\bin


             Per previous posts you want, whenever possible, to us a newer
             version of pg_dump to move a database from an older
        version(9.3) to
             a newer one(9.4). Therefore you should do your dump and restore
             using the pg_dump.exe and pg_restore.exe from the Bitanami bin
             directory. I would cd to the above directory and do:

             pg_dump -V
             pg_restore -V

             to make sure the programs are found and are the 9.4 versions.

             Then do:

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

             pg_restore -U postgres -p 5532 irll_project.out


        It appeared to work with this method, but it has now been
        running for
        almost 4 hours with no result. The db is not that large (probably v.
        small by most standards) and the .out file is ~200mb


    What is running, the dump or the restore?

The restore - I can see the dump .out file that was created at 16hr in
the postgresql/bin folder

So how are you determining it is running and that it is not doing anything?

What does the Postgres log for the 9.4 instance show?

             --
             Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
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

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 > your_output.txt

then use notepad to see the result.

But from your last reply, it looks like Postgres is finishing the restore by VACUUMing the database. That means it has to vacuum every table.

Since you never told us the info about whether you have a 32 or 64 bit system, how much memory, what processor speed, etc, it's hard to say how long it will take.

But if you have no errors in the postgresql log (after the restore completed), you should be fine.

It's usually located in<Bitnami dir>\data\pg_log

On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <killiandriscoll@gmail.com> wrote:

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

On 12/23/2015 11:17 AM, Killian Driscoll wrote:

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

    So how are you determining it is running and that it is not doing
    anything?

Since I ran the restore the windows shell has been 'active' with codes
lines running - I took that as being active.....!

So what do they say?

If I 'slow down' the code by using the scroll on the right I can see words from the db I recognise, but the rest is very long alphanumeric characters.

    What does the Postgres log for the 9.4 instance show?

Where is the log - here Control Panel\System and Security\Administrative
Tools in event viewer or elsewhere?

My guess in the Bitnami directory tree under logs/ or something similar.

I can't see a log folder.....

                      --
                      Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>>




             --
             Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

>OK - I see the logs there - the last log was almost 12 hrs ago, so no recent one.
 

That's not very helpful. Depending on how you've configured the logging, PostgreSQL may only create one log file a day.

Conventional thinking is It's what is IN the log file that is important.

On Wed, Dec 23, 2015 at 2:50 PM, Killian Driscoll <killiandriscoll@gmail.com> wrote:

On 23 December 2015 at 20:38, Melvin Davidson <melvin6925@gmail.com> wrote:

You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 > your_output.txt

then use notepad to see the result.

But from your last reply, it looks like Postgres is finishing the restore by VACUUMing the database. That means it has to vacuum every table.

Since you never told us the info about whether you have a 32 or 64 bit system, how much memory, what processor speed, etc, it's hard to say how long it will take.

But if you have no errors in the postgresql log (after the restore completed), you should be fine.

It's usually located in<Bitnami dir>\data\pg_log

OK - I see the logs there - the last log was almost 12 hrs ago, so no recent one.
 

On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <killiandriscoll@gmail.com> wrote:

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

On 12/23/2015 11:17 AM, Killian Driscoll wrote:

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

    So how are you determining it is running and that it is not doing
    anything?

Since I ran the restore the windows shell has been 'active' with codes
lines running - I took that as being active.....!

So what do they say?

If I 'slow down' the code by using the scroll on the right I can see words from the db I recognise, but the rest is very long alphanumeric characters.

    What does the Postgres log for the 9.4 instance show?

Where is the log - here Control Panel\System and Security\Administrative
Tools in event viewer or elsewhere?

My guess in the Bitnami directory tree under logs/ or something similar.

I can't see a log folder.....

                      --
                      Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>>




             --
             Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On 23 December 2015 at 20:59, Melvin Davidson <melvin6925@gmail.com> wrote:

>OK - I see the logs there - the last log was almost 12 hrs ago, so no recent one.
 

That's not very helpful. Depending on how you've configured the logging, PostgreSQL may only create one log file a day.

I didn't change a configuration, so it is how it came 'out of the box'

Conventional thinking is It's what is IN the log file that is important.

Apologies - I have looked, but what's in it doesn't appear to relate to the current pg_restore as it was logged many hours before so I didn't mention it, and its a 13mb file so I didn't attach it.

One thing I see it says
ERROR:  syntax error at or near "1" at character 1
STATEMENT:  1    0106000020E61000000100000001030000000100000049000000A2EA743BD46B1DC0EC7756B43F1

with the latter part being similar to the alphanumeric I see currently running in the windows shell. This is all a bit above my pay grade as you've probably gathered....

 

On Wed, Dec 23, 2015 at 2:50 PM, Killian Driscoll <killiandriscoll@gmail.com> wrote:

On 23 December 2015 at 20:38, Melvin Davidson <melvin6925@gmail.com> wrote:

You can rREDIRECT the output to a file!  EG: psql -U postgres -p 5532 > your_output.txt

then use notepad to see the result.

But from your last reply, it looks like Postgres is finishing the restore by VACUUMing the database. That means it has to vacuum every table.

Since you never told us the info about whether you have a 32 or 64 bit system, how much memory, what processor speed, etc, it's hard to say how long it will take.

But if you have no errors in the postgresql log (after the restore completed), you should be fine.

It's usually located in<Bitnami dir>\data\pg_log

OK - I see the logs there - the last log was almost 12 hrs ago, so no recent one.
 

On Wed, Dec 23, 2015 at 2:27 PM, Killian Driscoll <killiandriscoll@gmail.com> wrote:

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

On 12/23/2015 11:17 AM, Killian Driscoll wrote:

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

    So how are you determining it is running and that it is not doing
    anything?

Since I ran the restore the windows shell has been 'active' with codes
lines running - I took that as being active.....!

So what do they say?

If I 'slow down' the code by using the scroll on the right I can see words from the db I recognise, but the rest is very long alphanumeric characters.

    What does the Postgres log for the 9.4 instance show?

Where is the log - here Control Panel\System and Security\Administrative
Tools in event viewer or elsewhere?

My guess in the Bitnami directory tree under logs/ or something similar.

I can't see a log folder.....

                      --
                      Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>
                 <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>>




             --
             Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On 12/24/2015 12:03 AM, Killian Driscoll wrote:

        yeah, this one from Adrian, at 7:02am PST (Z-0800) this morning....

            Per previous posts you want, whenever possible, to us a
            newer version
            of pg_dump to move a database from an older version(9.3) to
            a newer
            one(9.4). Therefore you should do your dump and restore
            using the
            pg_dump.exe and pg_restore.exe from the Bitanami bin
            directory. I
            would cd to the above directory and do:

            pg_dump -V
            pg_restore -V

            to make sure the programs are found and are the 9.4 versions.

            Then do:

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

            pg_restore -U postgres -p 5532 irll_project.out


        that last needs to have -d newdbname     where newdbname has already
        been created, for instance, by...


    Aah, my mistake. Yes you need to specify the database to get the
    restore to work properly. Also explains why there is nothing in the
    logs.


OK - with the inclusion of stating the dbname the restore works, but not
correctly: what is restored is 24 of 48 tables and 1 of 22 views from
one schema and no tables from the other schema.

A log appeared at 0:08 last night (I'm at GMT +1), which I've attached.
Plus, I did the dump and restore again this morning and have attached
the text from the windows shell if that helps

Well the one from this morning shows(I did not look through whole thing) you restoring over existing database objects. I would say at this point the  best thing you can do is get to a known state on the 9.4 cluster you want to dump to. I am assuming you are not doing anything with the database irll_project on the 9.4 server at this point, correct?

If so, for the below keep track of exactly what you do and the order you do it, in case you need to post back here.

1) On the 9.4 server, while logged into another database on the server, say postgres do:

DROP DATABASE irll_project;

2) Using the 9.4 version of pg_dump dump the 9.3 version of irll_project.

3) Using the 9.4 version of pg_restore restore irll_project to the 9.4 server.

--
Adrian Klaver
adrian.klaver@aklaver.com