Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 24 December 2015 at 18:33, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

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

It worked - thank you very much for your time.

Regarding the file format used: I had used the pg_dump with .sql, but you
suggested .out. Is there a particular reason to use .out instead of .sql
when backing up?

>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> Killian Driscoll <killiandrisc...@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  > 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?

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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 10:58, Charles Clavadetscher <
clavadetsc...@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  > 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?

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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 11:07, John R Pierce <pie...@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  > 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* 
>
> (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  | 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
>
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 23 December 2015 at 11:07, John R Pierce <pie...@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  > 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* 
>>
>> (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  | 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)


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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 11:36, John R Pierce <pie...@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


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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 12/23/2015 06:13 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 14:56, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 12/23/2015 03:43 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 11:36, John R Pierce <pie...@hogranch.com
>> <mailto:pie...@hogranch.com>
>> <mailto:pie...@hogranch.com <mailto:pie...@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

>
>
>>
>> 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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 12/23/2015 03:43 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 11:36, John R Pierce <pie...@hogranch.com
>> <mailto:pie...@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

>
> 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.kla...@aklaver.com
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 12/23/2015 06:35 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 15:30, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 12/23/2015 06:13 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 14:56, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>>
>>     <mailto:adrian.kla...@aklaver.com>>> wrote:
>>
>>  On 12/23/2015 03:43 AM, Killian Driscoll wrote:
>>
>>  On 23 December 2015 at 11:36, John R Pierce
>> <pie...@hogranch.com <mailto:pie...@hogranch.com>
>>  <mailto:pie...@hogranch.com <mailto:pie...@hogranch.com
>> >>
>>  <mailto:pie...@hogranch.com
>> <mailto:pie...@hogranch.com> <mailto:pie...@hogranch.com
>> <mailto:pie...@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,

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:19, Melvin Davidson <melvin6...@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.kla...@aklaver.com>
> wrote:
>
>> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>>
>>> On 23 December 2015 at 20:07, Adrian Klaver <adrian.kla...@aklaver.com
>>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>>
>>> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>>>
>>> On 23 December 2015 at 16:02, Adrian Klaver
>>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>> <mailto:adrian.kla...@aklaver.com
>>> <mailto:adrian.kla...@aklaver.com>>> wrote:
>>>
>>>  On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>>
>>>  On 23 December 2015 at 15:47, Adrian Klaver
>>>  <adrian.kla...@aklaver.com
>>> <mailto:adrian.kla...@aklaver.com>
>>> <mailto:adrian.kla...@aklaver.com
>>>
>>> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>> <mailto:adrian.kla...@aklaver.com
>>> <mailto:adrian.kla...@aklaver.com>>
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@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.
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:26, Melvin Davidson <melvin6...@gmail.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 <
> killiandrisc...@gmail.com> wrote:
>
>> On 23 December 2015 at 20:19, Melvin Davidson <melvin6...@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;
>>>
>>
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.kla...@aklaver.com> wrote:
>>>
>>>> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>>>>
>>>>> On 23 December 2015 at 20:07, Adrian Klaver <adrian.kla...@aklaver.com
>>>>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>>>>
>>>>> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>>>>>
>>>>> On 23 December 2015 at 16:02, Adrian Klaver
>>>>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>> <mailto:adrian.kla...@aklaver.com
>>>>> <mailto:adrian.kla...@aklaver.com>>> wrote:
>>>>>
>>>>>  On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>>>>
>>>>>  On 23 December 2015 at 15:47, Adrian Klaver
>>>>>  <adrian.kla...@aklaver.com
>>>>> <mailto:adrian.kla...@aklaver.com>
>>>>> <mailto:adrian.kla...@aklaver.com
>>>>>
>>>>> <mailto:adrian.kla...@aklaver.com>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>So cd into:
>>>>>
>>>>>C:\Program Files\PostgreSQL\9.3\bin
>>>>>
>>>>>and try:
>>>>>
>>>>>pg_dump --help
>>>>>
>>>>>that will at least establish that the
>>>>>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 12/23/2015 11:09 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 20:07, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 16:02, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>>     <mailto:adrian.kla...@aklaver.com>>> wrote:
>>
>>  On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>
>>  On 23 December 2015 at 15:47, Adrian Klaver
>>  <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>>
>> <mailto:adrian.kla...@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?
>
Since I ran the restore the windows shell has been 'active' with codes
lines running - I took that as being active.!

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

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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 15:47, Adrian Klaver <adrian.kla...@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

>
>
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 20:14, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>
>>  <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>>  <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>>
>>
>>
>>
>>
>>  --
>>  Adrian Klaver
>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

> On 12/23/2015 11:03 AM, Killian Driscoll wrote:
>
>> On 23 December 2015 at 16:02, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 12/23/2015 06:50 AM, Killian Driscoll wrote:
>>
>> On 23 December 2015 at 15:47, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@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

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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:59, Melvin Davidson <melvin6...@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 1STATEMENT:  1
 
010620E610010001030001004900A2EA743BD46B1DC0EC7756B43F1*
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 <
> killiandrisc...@gmail.com> wrote:
>
>> On 23 December 2015 at 20:38, Melvin Davidson <melvin6...@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\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 <
>>> killiandrisc...@gmail.com> wrote:
>>>
>>>> On 23 December 2015 at 20:24, Adrian Klaver <adrian.kla...@aklaver.com>
>>>> wrote:
>>>>
>>>>> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>>>>>
>>>>>> On 23 December 2015 at 20:14, Adrian Klaver <
>>>>>> adrian.kla...@aklaver.com
>>>>>> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>> <mailto:adrian.kla...@aklaver.com
>>>>>> <mailto:adrian.kla...@aklaver.com>>
>>>>>>  <mailto:adrian.kla...@aklaver.com
>>>>>> <mailto:adrian.kla...@aklaver.com>
>>>>>>  <mailto:adrian.kla...@aklaver.com
>>>>>> <mailto:adrian.kla...@aklaver.com>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>  --
>>>>>>  Adrian Klaver
>>>>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>> <mailto:adrian.kla...@aklaver.com
>>>>>> <mailto:adrian.kla...@aklaver.com>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Adrian Klaver
>>>>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>> Adrian Klaver
>>>>> adrian.kla...@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.
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 20:38, Melvin Davidson <melvin6...@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\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 <
> killiandrisc...@gmail.com> wrote:
>
>> On 23 December 2015 at 20:24, Adrian Klaver <adrian.kla...@aklaver.com>
>> wrote:
>>
>>> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>>>
>>>> On 23 December 2015 at 20:14, Adrian Klaver <adrian.kla...@aklaver.com
>>>> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>> <mailto:adrian.kla...@aklaver.com
>>>> <mailto:adrian.kla...@aklaver.com>>
>>>>  <mailto:adrian.kla...@aklaver.com
>>>> <mailto:adrian.kla...@aklaver.com>
>>>>  <mailto:adrian.kla...@aklaver.com
>>>> <mailto:adrian.kla...@aklaver.com>>>
>>>>
>>>>
>>>>
>>>>
>>>>  --
>>>>  Adrian Klaver
>>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>> <mailto:adrian.kla...@aklaver.com
>>>> <mailto:adrian.kla...@aklaver.com>>
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Adrian Klaver
>>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>
>>>>
>>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 21:06, Killian Driscoll <killiandrisc...@gmail.com>
wrote:

> On 23 December 2015 at 20:59, Melvin Davidson <melvin6...@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 1STATEMENT:  1
>  
> 010620E610010001030001004900A2EA743BD46B1DC0EC7756B43F1*
> 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
>

I forgot to say, as this log is from earlier today, I guess it is from my
earlier attempts to do this before I sought help from this group?

>
>
>>
>> On Wed, Dec 23, 2015 at 2:50 PM, Killian Driscoll <
>> killiandrisc...@gmail.com> wrote:
>>
>>> On 23 December 2015 at 20:38, Melvin Davidson <melvin6...@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\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 <
>>>> killiandrisc...@gmail.com> wrote:
>>>>
>>>>> On 23 December 2015 at 20:24, Adrian Klaver <adrian.kla...@aklaver.com
>>>>> > wrote:
>>>>>
>>>>>> On 12/23/2015 11:17 AM, Killian Driscoll wrote:
>>>>>>
>>>>>>> On 23 December 2015 at 20:14, Adrian Klaver <
>>>>>>> adrian.kla...@aklaver.com
>>>>>>> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>>> <mailto:adrian.kla...@aklaver.com
>>>>>>> <mailto:adrian.kla...@aklaver.com>>
>>>>>>>  <mailto:adrian.kla...@aklaver.com
>>>>>>> <mailto:adrian.kla...@aklaver.com>
>>>>>>>  <mailto:adrian.kla...@aklaver.com
>>>>>>> <mailto:adrian.kla...@aklaver.com>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>  --
>>>>>>>  Adrian Klaver
>>>>>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>>> <mailto:adrian.kla...@aklaver.com
>>>>>>> <mailto:adrian.kla...@aklaver.com>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Adrian Klaver
>>>>>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> --
>>>>>> Adrian Klaver
>>>>>> adrian.kla...@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.
>>
>
>

[GENERAL] Transfer db from one port to another

[Killian Driscoll]

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

Re: [GENERAL] Transfer db from one port to another

[Killian Driscoll]

On 23 December 2015 at 10:05, Charles Clavadetscher <
clavadetsc...@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.

>
>
> Bye
>
> Charles
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@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
>

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Killian Driscoll]

> >
> > In terms of database structure and typing efficiency, it might be better
> > to restructure the lookup tables and create a new lookup table for each
> > of the three columns instead of one combined one?
>
> [Please keep you replies on-list - thanks!]
>

Sorry! Didn't mean that

>
> You'd better show us your table structures - we're only guessing
> otherwise. However, I think typing efficiency isn't important, but
> rather what your application needs to do with the data - you only type
> the query once. :-)
>

In terms of structure I'm going to go with separate lookup tables as they
are and should be distinct really.

I'm using pstgresql 9.3. I have set up the new lookup tables and now want
to alter the fk constraints but keep the data already there (the new lookup
tables with maintain the old pks). Below is one of the constraints:

CONSTRAINT macro_lookupg_macroscopic FOREIGN KEY (grain_id)
  REFERENCES irll.macro_lookup (macro_lookup_id) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE RESTRICT.

In 9.3 is there an alter constraint option or do I have to drop the
constraint and add the new one?

>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>

[GENERAL] Querying same lookup table with multiple columns based on another view

[Killian Driscoll]

I have a view with 15 columns and want to create another view based on a
join with another table with 15 columns that includes three columns that
reference one lookup table.

If I use the the below sql I get the error "column "macro_lookup_id"
specified more than once". I have read that I can rename the columns (I
renamed the tables as ml1, ml2, ml3) but can't figure out how to do this
but also use the select * to avoid writing out all rest of the column names.

CREATE OR REPLACE VIEW sample_macro AS
SELECT *
FROM sample
LEFT JOIN macroscopic ON query_srpnt_sample.samp_id = macroscopic.sample_id
LEFT JOIN macro_lookup AS ml1 ON ml1.macro_lookup_id = macroscopic.grain_id
LEFT JOIN macro_lookup AS ml2 ON ml2.macro_lookup_id = macroscopic.lustre_id
LEFT JOIN macro_lookup AS ml3 ON ml3.macro_lookup_id =
macroscopic.translucency_id
WHERE samp_id is not null;

What is the most efficient way (in terms of typing out column names) to
create this type of view?


Killian DriscoIl
IRC Postdoctoral Fellow
UCD School of Archaeology
University College Dublin

academia.edu/KillianDriscoll 
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll

Re: [GENERAL] Querying same lookup table with multiple columns based on another view

[Killian Driscoll]

On 17 November 2015 at 16:10, Killian Driscoll <killiandrisc...@gmail.com>
wrote:

> I have a view with 15 columns and want to create another view based on a
> join with another table with 15 columns that includes three columns that
> reference one lookup table.
>
> If I use the the below sql I get the error "column "macro_lookup_id"
> specified more than once". I have read that I can rename the columns (I
> renamed the tables as ml1, ml2, ml3) but can't figure out how to do this
> but also use the select * to avoid writing out all rest of the column names.
>
> CREATE OR REPLACE VIEW sample_macro AS
> SELECT *
> FROM sample
> LEFT JOIN macroscopic ON query_srpnt_sample.samp_id = macroscopic.sample_id
> LEFT JOIN macro_lookup AS ml1 ON ml1.macro_lookup_id = macroscopic.grain_id
> LEFT JOIN macro_lookup AS ml2 ON ml2.macro_lookup_id =
> macroscopic.lustre_id
> LEFT JOIN macro_lookup AS ml3 ON ml3.macro_lookup_id =
> macroscopic.translucency_id
> WHERE samp_id is not null;
>

In abbreviating the names for the post I just realised I confused the
names; it should have read.

CREATE OR REPLACE VIEW sample_macro AS
SELECT *
FROM query_srpnt_sample
LEFT JOIN macroscopic ON query_srpnt_sample.samp_id = macroscopic.sample_id
LEFT JOIN macro_lookup AS ml1 ON ml1.macro_lookup_id = macroscopic.grain_id
LEFT JOIN macro_lookup AS ml2 ON ml2.macro_lookup_id = macroscopic.lustre_id
LEFT JOIN macro_lookup AS ml3 ON ml3.macro_lookup_id =
macroscopic.translucency_id
WHERE samp_id is not null;

>
> What is the most efficient way (in terms of typing out column names) to
> create this type of view?
>
>
> Killian DriscoIl
> IRC Postdoctoral Fellow
> UCD School of Archaeology
> University College Dublin
>
> academia.edu/KillianDriscoll <https://ucd.academia.edu/KillianDriscoll>
> www.lithicsireland.ie
> ca.linkedin.com/in/killiandriscoll
>

[GENERAL] How can I change defined schema of linked tables when using Access linked table manager odbc connection

[Killian Driscoll]

I am using postgresql 9.3 on Windows 8 64, and am using Access as a
frontend data entry. In postgresql I have changed the schema from 'public'
to 'irll'. The linked table in Access are still linked as 'public' and I
need to update/change the connection of the tables to match the new schema
in the postgresql db.

I can easily set up a new odbc connection with the new schema, but if I do
this I will lose the data input forms I have set up in Access. How can I
change the defined schema of the linked tables?

Killian DriscoIl
IRC Postdoctoral Fellow
UCD School of Archaeology
University College Dublin

academia.edu/KillianDriscoll 
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll

[GENERAL] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Killian Driscoll]

I am using postgresql 9.3 with pgadmin III, and Access 2013 as a front end
for data entry using (ODBC connection) linked tables, on a Windows 8.1 (64).

I have one main database I am developing on a localhost:5432, with four
other test databases I had on the same localhost. A few weeks ago the four
test databases disappeared from the list in pgadmin III and using the shell
command to list the databases, only the main database showed up.

A week ago, the four test databases reappeared and were apparently
functional (in pgadmin I viewed the tables and they appeared fine). I
turned off pgadmin and restarted, and the four test dataabases disappeared
again.

Today, I turned on pgadmin and the four appeared again - I took a
screenshot to confirm I am not hallucinating (!). Again, I turned off
pgadmin, and restarted and they disappeared again.

Re: [GENERAL] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Killian Driscoll]

On 4 August 2015 at 17:02, Adrian Klaver adrian.kla...@aklaver.com wrote:

 On 08/04/2015 07:48 AM, Killian Driscoll wrote:

 CCing list

 FYI, I will be away from my computer. Someone else will have to follow up
 to my questions below.



 So that is not the server you set up, correct?


 I am using the postgresql on localhost 5432. The OpenGeo\pgsql is
 something I set up to test, but ended up not using it. It runs on a
 localhost 8000, but could uninstall it as I'm not going to use that
 program.


 Can you access it and see what is in it?


 When I access it on localhost 8000 it shows the Geoserver with the test
 tables I included from my main db


 So in pgAdmin do you see both servers?


In pgadmin only one server - 5432 - is listed. (I don't remember how I
imported the table to the 8000 Geoserver: I think it was via QGIS)






 What does the Postgres log show when you connect? - where can I
 find the
 postgres log?


 http://www.pgadmin.org/docs/1.20/status.html


 The log shows the main db only:

 FATALthe database system is starting up
 LOGdatabase system was interrupted; last known up at 2015-07-31
 03:37:41 EDT
 LOGdatabase system was not properly shut down; automatic recovery in
 progress
 LOGrecord with zero length at 0/5B392220
 LOGredo is not required
 LOGdatabase system is ready to accept connections
 LOGautovacuum launcher started
 ERRORschema pgagent does not exist
 STATEMENTSELECT pgagent.pgagent_schema_version()
 ERRORrelation pgagent.pga_jobagent does not exist at character 59
 STATEMENTINSERT INTO pga_tmp_zombies (jagpid) SELECT jagpid   FROM
 pgagent.pga_jobagent AG   LEFT JOIN pg_stat_activity PA ON jagpid=pid
 WHERE pid IS NULL
 ERRORrelation pgagent.pga_jobagent does not exist at character 13
 STATEMENTINSERT INTO pgagent.pga_jobagent (jagpid, jagstation)
 SELECT pg_backend_pid(), 'KillianAcer.homestation'
 ERRORschema pgagent does not exist
 STATEMENTSELECT pgagent.pgagent_schema_version()
 LOGcould not receive data from client: No connection could be made
 because the target machine actively refused it.

 ERRORsyntax error at or near   at character 1
 STATEMENT
  l
  \l meta command\l meta command\l meta command
  select osm from pg_database
  select osm from pg_database;
 LOGcould not receive data from client: No connection could be made
 because the target machine actively refused it.


 Well this seems to be your problem.

 Have you changed your pg_hba.conf recently?


I don't think so: if that file is here C:\Program Files\PostgreSQL\9.3\data
I don't think I made any changes



 LOGinvalid length of startup packet
 ERRORrelation public.gt_pk_metadata does not exist at character 15
 STATEMENTSELECT * FROM public.GT_PK_METADATA WHERE 1 = 0



 What user are you connecting as, and what privileges does that
 user
 have? - I had set it up as one user/owner.


 So from here:

 http://www.pgadmin.org/docs/1.20/main.html

 You can find the users(roles) and their privileges.


 The is one user - me as postgres, a superuser




 Killian Driscoll
 Banting Postdoctoral Fellow
 Département d'anthropologie
 Université de Montréal




 --
 Adrian Klaver
 adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com




 --
 Adrian Klaver
 adrian.kla...@aklaver.com