Re: Transfer db from one port to another

On 23 December 2015 at 20:26, Melvin Davidson <melvin6925(at)gmail(dot)com> 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(at)gmail(dot)com> wrote:
>
>> On 23 December 2015 at 20:19, Melvin Davidson <melvin6925(at)gmail(dot)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;
>>>
>>
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(dot)klaver(at)aklaver(dot)com> wrote:
>>>
>>>> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>>>>
>>>>> On 23 December 2015 at 20:07, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>>>>
>>>>> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>>>>>
>>>>> On 23 December 2015 at 16:02, Adrian Klaver
>>>>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
>>>>>
>>>>> On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>>>>
>>>>> On 23 December 2015 at 15:47, Adrian Klaver
>>>>> <adrian(dot)klaver(at)aklaver(dot)com
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>>>>>
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>>>>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Adrian Klaver
>>>>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>> Adrian Klaver
>>>> adrian(dot)klaver(at)aklaver(dot)com
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.
>