Обсуждение: Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)
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.
On Tue, Aug 4, 2015 at 2:28 PM, Killian Driscoll <killiandriscoll@gmail.com> wrote:
Regards,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).
OK.
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.
Could you be more specific about this. Using shell command to list the database, you got one database OR all databases (?)
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.
Did you restarted the DB after pgAdmin turnoff.
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.
Dinesh
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 4 August 2015 at 17:02, Adrian Klaver <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)
Well this seems to be your problem.
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.
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
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