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 -E
To see what that does :
man psql
sandhya mentioned :
=> Hi...
=> Is there any equivalent sql commands/queries for
=> ' \l ' (which list out all the databases) and
=> ' \dt ' ( Which lists all the Tables of the current database)
=> If anybody knows Please tell me.Or If there is any
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
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 t
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 tr
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
[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
=> 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.
--
Confiden
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
Hi all,
I'm trying to create some kind of table version control
system for approximately 300 postgres databases
ranging in version from 7.1.2 to 7.3.4.
I compared the "pg_dump -s" output between
the various versions of databases, but the format is inconsistent,
and I can't do diff's to check th
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 = NEW
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 f
=> 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 us
Hi all,
Just another question on all this :
When will postgres have win32 native
support. I've been gathering information
from bits and pieces I found on the net,
but never got an official answer.
Some said it will be in 7.4, but it's not there,
and from what I gathered about a month ago
was th
And the world rejoiced as Robert Treat said :
=> That's about as definitive as it gets (unless you are going to write the
=> code yourself)
Hehe, I've actually done something like that before, and wouldn't
mind having a go at it, but my immediate problem is solved. At
least when people ask me what
s quite well. I haven't 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
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 sq
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
th
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 tim
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 in
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
a
Oops, my 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 statement in
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' rest
ld.
I now manually use this dump command :
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 yo
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
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 wor
[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 st
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
tho
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" dump
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
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 7
or so for +- 450 tables on my machine.
It may break if you have 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 :
=> fo
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
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 i
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
37 matches
Mail list logo
