Hi all,
I've got a postgres 7.3.4 database. and somehow the pg_control file got
corrupted
when the machine ran out of space, and someone ran pg_resetxlog -f.
So basically the database starts up, but I can only see template1 and template0.
Is there a way to link in the data that is in the base
= This is really critical for me.
= I found a possible solution =
= http://archives.postgresql.org/pgsql-patches/2005-02/msg00124.php
I managed to get this working by just adding the return true;
and recompiling. So yes, it works. If you need more help than that,
let me know.
--
Marcos de Lima Takayas mentioned :
= We had an accident with a table where all tuples where deleted.
= We have not run VACUUM yet and it seems like the datas are in place
= in the base directory.
= Our backup were not active when this happened.
= I have seen some talk (Mr.Zé Carlos) about that it
[EMAIL PROTECTED] mentioned :
=
=
= Hi
=
= I have tried it yesterday to escaoe the double qutoes and I get the
= error:
=
= [test] su - postgres -c echo \copy test from '/data/log/bla';\ |
= psql logdb
= Unmatched .
=
= somehow does not want to work.
su - postgres -c echo \copy
Just disable password checking in pg_hba.conf.
mad mentioned :
= I have lost the superuser (user postgres) password, but I still have
= the unix root password. Is there anything I can do to reset the
= postgres user's password (short of reinstalling Postgres)?
=
= Obviously, I can su to unix
I have a table in the databases I work with,
that contains two text columns with XML data
stored inside them.
This table is by far the biggest table in the databases,
and the text columns use up the most space.
I saw that the default storage type for text columns is
EXTENDED which, according to
Jim C. Nasby mentioned :
= Are you seeing much gain using ztext over using EXTENDED?
When storage is defined external, there is a visible gain,
but not really significant enough to make me want to use it.
And I had instances where not all the rows would uncompress again
afterwards. I want to
sandhya mentioned :
= No...What i need is...
= I want to know the equivalent sql queries for \l and \dt commands in order
= to list the databases and tables.
= I want to do it through programming
From the psql man-page :
-E
--echo-hidden
Echoes the actual queries generated by \d and
Cédric Buschini mentioned :
= psql=`psql -h XXX.XXX.XXX.XXX. -U USER -D DATABASE -c 'SELECT COUNT(*) FROM
'$3''`
=
= this is not perform using a call with crontab, but launching the script
(./SCRIPT.SH) works...
=
= to check if it works, I added an echo $psql
=
=
= Any ideas ???
psql=`psql
some exotic definitions that I didn't test for,
but I think it's pretty solid as it is here.
Kind Regards
Stefan
Stef mentioned :
= Here's my final solution that runs in less than a minute for +- 543 tables :
= for x in $(psql -tc select relname from pg_class where relkind = 'r' and
relname
Jim Buttafuoco mentioned :
= I use dblink to attach to both databases and query pg_namespace, pg_class,
pg_attribute ... to get the diffs. See
= attached as an example. look for the dblink_connect lines to specify your
database. You will need to install
= contrib/dblink. I used this with
Hi all,
I've got a master database with many other databases that
all have (or is supposed to have) the same exact same schema
as the master database (the master database is basically an empty
template database containing the schema definition).
The problem is that none of the schemas actually
Markus Schaber mentioned :
= There are (at least) two independently developed pgdiff applications,
= they can be found at:
=
= http://pgdiff.sourceforge.net/
=
= http://gborg.postgresql.org/project/pgdiff/projdisplay.php
Thanks a lot!
= I did not try the first one, but the latter one worked on
[EMAIL PROTECTED] mentioned :
= Are you just synching the schemas, or do you also need to synch the data?
Schemas now, data later.
To do the data part, I'm thinking of using slony, because it seems to be able to
do pretty much everything I need from that side. But, unfortunately I can't
even
John DeSoi mentioned :
= Develop a function that builds a string describing the tables/schemas
= you want to compare. Then have your function return the md5 sum of the
= string as the result. This will give you a 32 character value you can
= use to determine if there is a mismatch.
OK, this
Markus Schaber mentioned :
= Some weeks ago, I posted here a script that uses psql to create split
= dumps. Maybe you can reuse some of its logics to create per-table
= md5sums for all tables in a database automatically.
Thanks, but I've got something very similar to this already. I almost
Tom Lane mentioned :
= The problem I have with this, is that I have to run the command per table,
=
= Why?
=
= If the problem is varying order of table declarations, try 8.0's
= pg_dump.
Yes, this will solve the global schema check, but I will still need to split
it into per table dumps , to
John DeSoi mentioned :
= I'm not sure you can use \d directly, but if you startup psql with the
= -E option it will show you all the SQL it is using to run the \d
= command. It should be fairly easy to get the strings you need from the
= results of running a similar query. The psql source is a
:
pg_dump | sed 's:N:¬:g' | gzip dump.gz
and do the reverse sed to restore.
Stef mentioned :
= mike g mentioned :
= = That could be a bug. How are you dumping the data? pg_dump? Select
= = query? How are you restoring the data? psql?
=
= Dumping:
= pg_dump -Ft | gzip dump.tgz
Tom Lane mentioned :
= This is demonstrably not so. You might have trouble with data coming
= from somewhere else, if the source doesn't understand the quoting rules
= for COPY data. But I can dump and restore a table containing '\N' and
= variants of that without any trouble.
Here's what I did
mike g mentioned :
= That could be a bug. How are you dumping the data? pg_dump? Select
= query? How are you restoring the data? psql?
Dumping:
pg_dump -Ft | gzip dump.tgz
Restoring:
zcat dump.tgz | pg_restore -Ft |psql
OR
tar xvfz dump.tgz
perl -pi -e 's/\$\$PATH\$\$/$ENV{PWD}/g'
Hi all,
I'm stuck with a problem,about which I couldn't find
much info. I'm sure somebody must have encountered this before.
I've got a NOT NULL column of type text.
It happens that freehand data gets inserted into this table, and
it typically contains over 1000 rows with the value '\N' , and
Oops, my Reply all button doesn't work...
Hilary Forbes mentioned :
= Can we go back to the beginning here?! If you are doing updates to remove the \N,
why are you allowing them to get into the database in the first place? Why not get
rid of them in your UPDATE statement using the replace
Hi all,
I've got a situation on certain machines where approximately
70 postmasters are running. Each with one database, and one
data directory.
This is fine most of the time, except for a certain time of the month,
when a very large consolidation process has to run on each database.
At this
Tom Lane mentioned :
= Get out your backup tapes, because what you have on disk now is just a
= pile of inconsistent bits.
I haven't actually done anything but SELECT's from the shared postmaster,
and it was a test machine. Phew!! Glad I asked first...
I think the way I'll do it, is by using
Hi all,
I've switched on log_statement in postgresql.conf
for debugging purposes. I tried logging connections
and pids as well, but I need to match up the logged statements
to specific connections.
The postmaster logs to a separate log file, but at the moment
it's impossible to tell which
Stef mentioned :
= The postmaster logs to a separate log file, but at the moment
= it's impossible to tell which sql comes from which connection.
= Is there an easy way to accomplish this?
Erm... sorry ,
It appears my postgresql.conf is not an original. It didn't have
the log_pid option
been able to get this working
in debian yet. It just hangs. Very nice application though :
http://fabforce.net/downloads.php
C
Stef
pgp0.pgp
Description: PGP signature
= This is extremely hard to believe. I can see no way that pg_dump will
= do that unless you explicitly ask for it (-d or -D switch, or one of the
= long variants of same).
I know of a lot of people that confuses the -d switch as an option
indicating the database name to follow. It is also
Correction on the function :
The function currently on the database did has
select int4(description) + 1 into v_new_version from pg_description
where objoid = NEW.attrelid;
in stead of
select int4(description) into v_new_version from pg_description
where objoid =
Thanks guys,
I had a feeling this was the case, but wasn't sure.
The one-version pg_dump looks like a winner.
Regards
Stefan
##START##
= Rod Taylor [EMAIL PROTECTED] writes:
= What I did next, is put a trigger on pg_attribute that should, in theory,
= on insert and update, fire up a function
If one has upgraded postgres from 7.1.2 to 7.3.1,
how does one convert all the triggers that represent
7.1.2 foreign keys to actual foreign keys.
What I really want to have, is that when I dump the new
upgraded database, that the tables will be dumped with the
original foreign key syntax as they
Hi list,
Having a problem shutting down
a database (ver 7.3.1)
I basically restart a certain database every night
because it always used to be the easy solution,
to get rid of unwanted zombie connections,
left by people who close front end aps by
switching off their computers.
The problem now
33 matches
Mail list logo
