Re: Transfer db from one port to another

[Killian Driscoll <killiandriscoll@xxxxxxxxx>]

On 23 December 2015 at 20:26, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

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@xxxxxxxxx> wrote:

On 23 December 2015 at 20:19, Melvin Davidson <melvin6925@xxxxxxxxx> 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;

OK - I get this
Server [localhost]:
Database [postgres]:
Port [5432]: 5532
Username [postgres]:
psql (9.3.4, server 9.4.4)
WARNING: psql major version 9.3, server major version 9.4.
         Some psql features might not work.
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# SELECT datname,
postgres-#        pid as pid,
postgres-#        client_addr,
postgres-#        usename as user,
postgres-#        query,
postgres-#        CASE WHEN waiting = TRUE
postgres-#             THEN 'BLOCKED'
postgres-#             ELSE 'no'
postgres-#         END as waiting,
postgres-#        query_start,
postgres-#        current_timestamp - query_start as duration
postgres-#   FROM pg_stat_activity
postgres-#  WHERE pg_backend_pid() <> pid
postgres-# ORDER BY datname,
postgres-#          query_start;
   datname    | pid  | client_addr |   user   |
                      query
   | waiting |        query_start         |   duration
--------------+------+-------------+----------+---------------------------------
--------------------------------------------------------------------------------
---+---------+----------------------------+--------------
 irll_project | 8088 | 127.0.0.1   | postgres | SELECT defaclacl FROM pg_catalog
.pg_default_acl dacl WHERE dacl.defaclnamespace = 19228::oid AND defaclobjtype='
T' | no      | 2015-12-23 17:37:18.295+01 | 02:46:37.17
 postgres     | 5084 | 127.0.0.1   | postgres | SELECT setting FROM pg_settings
WHERE name IN ('autovacuum', 'track_counts')
   | no      | 2015-12-23 17:37:02.469+01 | 02:46:52.996
(2 rows)


postgres=#

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

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

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

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

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

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

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

        <mailto:adrian.klaver@xxxxxxxxxxx>>




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




    --
    Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
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.