Thread: re-instalation
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
-----------------------------------------------------------------
El Vie 19 Ene 2001 17:36, Len Morgan escribió: > > 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? > > You have apparently used pg_access at least once. It creates these tables > for itself. Yes, but they shouldn't be visable (am I wrong?), just like I shouldn't be seeing with pgaccess, and conecting to the same db, tables like: pg_proc pg_inherits pg_type, etc. I didn't have this problems before doing this re-instalation. 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 -----------------------------------------------------------------
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
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 -----------------------------------------------------------------
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!
"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
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
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/
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 -----------------------------------------------------------------
> 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? > You have apparently used pg_access at least once. It creates these tables for itself. Len Morgan