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

On 08/04/2015 01:58 AM, Killian Driscoll wrote:
> 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.

So do you have more then one Postgres cluster/instance on the machine?


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


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/04/2015 06:25 AM, Killian Driscoll wrote:

CCing list

Please do not top post, thanks.

> All the databases are one one server on localhost 5432

And you have verified this my looking at the Windows process monitor?

The reason I keep asking is that is looks very much like you are
connecting to two different clusters. One that has only your main
database and one with it and the four test databases.

Does the data in the main database look the same in both cases?

What does the Postgres log show when you connect?

Another theory is that this is a user privileges thing.

What user are you connecting as, and what privileges does that user have?



>
> Killian Driscoll
> Banting Postdoctoral Fellow
> Département d'anthropologie
> Université de Montréal
>
> umontreal.academia.edu/KillianDriscoll
> <http://umontreal.academia.edu/KillianDriscoll>
> www.lithicsireland.ie <http://www.lithicsireland.ie>
> ca.linkedin.com/in/killiandriscoll
> <http://ca.linkedin.com/in/killiandriscoll>
>
> On 4 August 2015 at 15:23, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 08/04/2015 01:58 AM, Killian Driscoll wrote:
>
>         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.
>
>
>     So do you have more then one Postgres cluster/instance on the machine?
>
>
>
>
>         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.
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/04/2015 06:48 AM, Killian Driscoll wrote:
> Please do not top post, thanks. - no idea what that means.....

See here:

https://en.wikipedia.org/wiki/Posting_style#Top-posting

vs

https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

or

https://en.wikipedia.org/wiki/Posting_style#Bottom-posting

which are the preferred styles for this list.

>
> And you have verified this my looking at the Windows process monitor? -
> you mean task manager: if so, I also see Boundless\OpenGeo\pgsql\bin
> running a postgresql server

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

Can you access it and see what is in it?

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

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

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


--
Adrian Klaver
adrian.klaver@aklaver.com

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?

>
>
>
>         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:
>
> FATAL    the database system is starting up
> LOG    database system was interrupted; last known up at 2015-07-31
> 03:37:41 EDT
> LOG    database system was not properly shut down; automatic recovery in
> progress
> LOG    record with zero length at 0/5B392220
> LOG    redo is not required
> LOG    database system is ready to accept connections
> LOG    autovacuum launcher started
> ERROR    schema "pgagent" does not exist
> STATEMENT    SELECT pgagent.pgagent_schema_version()
> ERROR    relation "pgagent.pga_jobagent" does not exist at character 59
> STATEMENT    INSERT 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
> ERROR    relation "pgagent.pga_jobagent" does not exist at character 13
> STATEMENT    INSERT INTO pgagent.pga_jobagent (jagpid, jagstation)
> SELECT pg_backend_pid(), 'KillianAcer.homestation'
> ERROR    schema "pgagent" does not exist
> STATEMENT    SELECT pgagent.pgagent_schema_version()
> LOG    could not receive data from client: No connection could be made
> because the target machine actively refused it.
>
> ERROR    syntax 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;
> LOG    could 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?

>
> LOG    invalid length of startup packet
> ERROR    relation "public.gt_pk_metadata" does not exist at character 15
> STATEMENT    SELECT * 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/04/2015 08:12 AM, Killian Driscoll wrote:
>
> On 4 August 2015 at 17:02, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@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)


So what happens if you use the command line program psql and connect to
the server on port 5432?

Assuming you can connect what does \l from the psql command line show?



--
Adrian Klaver
adrian.klaver@aklaver.com