I had to re-compile and re-install postgresql-7.1-beta1.
I changed the directory where it was installed from /usr/local/pgsql to 
/dbs/postgres/. After re-installing I copied the data/ directory that was 
under the old instalation to where I have the new instalation.
After a bit of work I got it to work, but when I conect to any of the 
databases I see tables that don't belong there:

postgres@ultra31:~ > psql horde
Welcome to psql, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
horde=# \dt
         List of relations
      Name       | Type  |  Owner
-----------------+-------+----------
 active_sessions | table | postgres
 imp_addr        | table | postgres
 imp_pref        | table | postgres
 pga_forms       | table | postgres
 pga_queries     | table | postgres
 pga_reports     | table | postgres
 pga_schema      | table | postgres
 pga_scripts     | table | postgres
(8 rows)


Any ideas why those pg* tables are there?

TIA.

-- 
System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martï¿½n Marquï¿½s			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Fri, 19 Jan 2001 17:23:22 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
re-instalation
On Fri, 19 Jan 2001, Martin A. Marques wrote:

> I had to re-compile and re-install postgresql-7.1-beta1.
> I changed the directory where it was installed from /usr/local/pgsql to
> /dbs/postgres/. After re-installing I copied the data/ directory that was
> under the old instalation to where I have the new instalation.
> After a bit of work I got it to work, but when I conect to any of the
> databases I see tables that don't belong there:

You probably should have used pg_dumpall to backup the databases and
restore them after the rebuild.  That's a more reliable way of migrating
your data.

> horde=# \dt
>          List of relations
>       Name       | Type  |  Owner
> -----------------+-------+----------
>  active_sessions | table | postgres
>  imp_addr        | table | postgres
>  imp_pref        | table | postgres
>  pga_forms       | table | postgres
>  pga_queries     | table | postgres
>  pga_reports     | table | postgres
>  pga_schema      | table | postgres
>  pga_scripts     | table | postgres
> (8 rows)
>
> Any ideas why those pg* tables are there?

Those are system tables created and used by pgAccess.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Never look a gift horse in the mouth.
		-- Saint Jerome


Fri, 19 Jan 2001 20:32:41 -0500 (EST)
"Brett W. McCoy" <bmccoy@chapelperilous.net>
Re: re-instalation
El Vie 19 Ene 2001 22:32, Brett W. McCoy escribiï¿½:
> On Fri, 19 Jan 2001, Martin A. Marques wrote:
> > I had to re-compile and re-install postgresql-7.1-beta1.
> > I changed the directory where it was installed from /usr/local/pgsql to
> > /dbs/postgres/. After re-installing I copied the data/ directory that was
> > under the old instalation to where I have the new instalation.
> > After a bit of work I got it to work, but when I conect to any of the
> > databases I see tables that don't belong there:
>
> You probably should have used pg_dumpall to backup the databases and
> restore them after the rebuild.  That's a more reliable way of migrating
> your data.

The problem was that the server got downgraded from Solaris 8 to Solaris 7 
and the binaries didn't work, so I recompiled. There was no way of using 
pg_dump because I couldn't get the postmaster up.

> > horde=# \dt
> >          List of relations
> >       Name       | Type  |  Owner
> > -----------------+-------+----------
> >  active_sessions | table | postgres
> >  imp_addr        | table | postgres
> >  imp_pref        | table | postgres
> >  pga_forms       | table | postgres
> >  pga_queries     | table | postgres
> >  pga_reports     | table | postgres
> >  pga_schema      | table | postgres
> >  pga_scripts     | table | postgres
> > (8 rows)
> >
> > Any ideas why those pg* tables are there?
>
> Those are system tables created and used by pgAccess.

They never apeared before.

And by the way, I see all the system tables when looking at any database with 
pgaccess (another thing that didn't happen before).

Saludos... :-)


-- 
System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martï¿½n Marquï¿½s			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Sat, 20 Jan 2001 16:29:39 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
Re: re-instalation
On Sat, 20 Jan 2001, Martin A. Marques wrote:

> > You probably should have used pg_dumpall to backup the databases and
> > restore them after the rebuild.  That's a more reliable way of migrating
> > your data.
>
> The problem was that the server got downgraded from Solaris 8 to Solaris 7
> and the binaries didn't work, so I recompiled. There was no way of using
> pg_dump because I couldn't get the postmaster up.

Doh!  You should smack the admin who downgraded without warning you so you
could take appropriate measures! :-)  Why did you downgrade in the
firstplace?  Just curious...

The problem with just moving your database to the new location is that
there are location dependencies built into it when you use initdb to
initialize it, so it's not reliable.  Of course, if you have no other
choice, you have no ther choice... but this may be why hidden tables are
suddenly showing up everywhere.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
I'm a Lisp variable -- bind me!


Sat, 20 Jan 2001 14:41:51 -0500 (EST)
"Brett W. McCoy" <bmccoy@chapelperilous.net>
Re: re-instalation
"Brett W. McCoy" <bmccoy@chapelperilous.net> writes:
> On Sat, 20 Jan 2001, Martin A. Marques wrote:
>> The problem was that the server got downgraded from Solaris 8 to Solaris 7
>> and the binaries didn't work, so I recompiled. There was no way of using
>> pg_dump because I couldn't get the postmaster up.

> The problem with just moving your database to the new location is that
> there are location dependencies built into it when you use initdb to
> initialize it, so it's not reliable.

AFAIK, there are *not* any location dependencies in a standard PG
database (other than path names associated with alternate database
locations, if you are so foolish as to use hard-wired alternate-location
pathnames).  So in theory Martin should have been able to tar up the
entire $PGDATA tree and move it somewhere else.  I'm not sure why he's
reporting a problem, but I don't think that's it.

A more plausible line of thought is that there is some environmental
difference between the old and new setup --- perhaps PG was compiled
with different options (with/without locale or multibyte support), or
is being run under a different LOCALE environment, etc.  I'm not sure
exactly why that sort of thing would manifest itself as re-appearance
of tables that had been deleted in the old setup, but that's my bet.

Before you write this off as unreasonable, consider that changing
locales renders indexes on text columns effectively corrupt, if there
are any entries whose sort order changes in the new locale.  I don't
quite see the chain of events from corrupt indexes on pg_class to
unexpected table entries, but it doesn't seem as unlikely as blaming
the problem on a physical move of the database directory tree.

			regards, tom lane

Sat, 20 Jan 2001 16:09:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: re-instalation 
On Sat, 20 Jan 2001, Tom Lane wrote:

> > The problem with just moving your database to the new location is that
> > there are location dependencies built into it when you use initdb to
> > initialize it, so it's not reliable.
>
> AFAIK, there are *not* any location dependencies in a standard PG
> database (other than path names associated with alternate database
> locations, if you are so foolish as to use hard-wired alternate-location
> pathnames).  So in theory Martin should have been able to tar up the
> entire $PGDATA tree and move it somewhere else.  I'm not sure why he's
> reporting a problem, but I don't think that's it.

Ah, right... I misunderstood and thought he had also changed to a
different version because of OS incompatilities.  Never mind. :-)

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Vax Vobiscum


Sat, 20 Jan 2001 16:34:05 -0500 (EST)
"Brett W. McCoy" <bmccoy@chapelperilous.net>
Re: re-instalation 
Martin A. Marques writes:

> > > Any ideas why those pg* tables are there?
> >
> > Those are system tables created and used by pgAccess.
>
> They never apeared before.

You probably never ran pgaccess before.

> And by the way, I see all the system tables when looking at any database with
> pgaccess (another thing that didn't happen before).

Go to the menu Databases -> Preferences and check off "View system
tables".

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Sat, 20 Jan 2001 22:43:37 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Re: re-instalation
El Sï¿½b 20 Ene 2001 18:43, Peter Eisentraut escribiï¿½:
> Martin A. Marques writes:
> > > > Any ideas why those pg* tables are there?
> > >
> > > Those are system tables created and used by pgAccess.
> >
> > They never apeared before.
>
> You probably never ran pgaccess before.

No, I used to run pgaccess daily.

> > And by the way, I see all the system tables when looking at any database
> > with pgaccess (another thing that didn't happen before).
>
> Go to the menu Databases -> Preferences and check off "View system
> tables".

Great Peter!! That did it! 
(I'm clueless!!!! :-P )

-- 
System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martï¿½n Marquï¿½s			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Sat, 20 Jan 2001 18:58:52 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
Re: [HACKERS] Re: re-instalation