my
colleagues knowledge allowed.
I hope someone can suggest something - I'm desperate! It'll take weeks
to re-input that data automatically.
Thanks
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (
Late as it is, I'll try this monday. I hope it works, other suggestions
still welcome of course, thanks very much.
On Fri, 27 Jun 2003, Tom Lane wrote:
> Sam Barnett-Cormack <[EMAIL PROTECTED]> writes:
> > [ accidentally clobbered pg_control ]
>
> It sounds like the m
tart again.
Then do *nothing* until you have dumped the db to a file, and cleanly
resotred from it. You should also manually check for inconsistencies,
although if you have correctly used contraints it should barf on input
if there are any. Assuming your DB is fully normalised.
Hope that
re, just in case.
> >
> > 4) Trial and error (possibly) until you get the server to start again.
> > Then do *nothing* until you have dumped the db to a file, and cleanly
> > resotred from it. You should also manually check for inconsistencies,
> > although if you have c
;-rw---1 postgres postgres 8192 Jun 30 08:15 35623486.5
> >-rw---1 postgres postgres 512 Jun 30 04:57 35623488
> >-rw---1 postgres postgres 147456 Jun 30 04:57 35623490
> >-rw---1 postgres postgres65776 Jun 29 11:44 pg_internal.init
>
On Tue, 1 Jul 2003, Tom Lane wrote:
> Sam Barnett-Cormack <[EMAIL PROTECTED]> writes:
> > On Tue, 1 Jul 2003, Michiel Lange wrote:
> >> _Could_ it be that you're hitting a filesystem limit here? I am not 100%
> >> certain, but I believe ext2 by default supp
y than that.
I have a really quite large DB, but I am *really* struggling with 1GB
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
--
On Thu, 3 Jul 2003, scott.marlowe wrote:
> On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote:
>
> > On Thu, 3 Jul 2003, Daniel Seichter wrote:
> >
> > > Today memory isn't expensive so with 512MB I can be shure, every user on
> > > this server might have enou
On Thu, 3 Jul 2003, scott.marlowe wrote:
> On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote:
>
> > On Thu, 3 Jul 2003, scott.marlowe wrote:
> >
> > > We went from 512 Meg to 1.5 Gig and the change was tremendous. That box
> > > ran Apache/Postgresql/auth_ldap/L
>
>
>
>
> Jean-Arthur Silve
> EuroVox
> 4, Place Félix Eboué
> 75583 Paris Cedex 12
> T : +33 1 44670505
> F : +33 1 44670519
>
>
>
>
>
> ---(end of broadcast)---------------
> TIP 3: if posting/reading through Usenet, please send an
On Mon, 7 Jul 2003, Nick Fankhauser wrote:
> -There is plenty of disk space available.
Does it stop at a filesize limit imposed by the OS or filesystem, such
as 2.0GB as commonly found on linux, or NFS?
Sam
---(end of broadcast)---
TIP 8: explain
On Thu, 31 Jul 2003, Joe Maldonado wrote:
> I know in Oracle you can partition the tables into logical subsets and
> was wondering if this was also possible in postgres.
I *think* that a partial index is probably your best bet.
--
Sam Barnett-Cormack
Software Dev
also?
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
s major
performance and admin benefits.
Only try it if you have a lot of time and/or already know linux well. If
you have a *lot* of time, you will gain the knowledge as you go. If you
decide to, I'll see you on *that* mailing list.
Also, I recommend slackware as an LFS base.
--
Sam
der of 12 hours to finish.
I've been dumping and restoring a very very big database, with indexes,
with no such problems. The table details that get dumped setup the
indexes, and AIUI the indexes are kept up-to-date as items are added.
--
Sam Barnett-Cormack
Software Developer
meone has encountered this problem before or knows a solution
> please reply.
>
>
>
> Jean-Michel Gaudel
>
> [EMAIL PROTECTED]
>
>
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Wed, 20 Aug 2003, Dani Oderbolz wrote:
> Guys,
> be careful,
> it seems that several Mails in the List have contained
> a virus - I guess its sobig.f.
Or that a copy of sobig learned the mailing list address and masqueraded
as being from the list.
--
Sam Barnett-Cormack
Softwa
neral question:
Can the planner tell which index to use if there is more than one that
fits the bill? Like if there is a full index and one or more partial
indexes on a field, can it determine which to use for a given query?
--
Sam Barnett-Cormack
Software Developer |
esblabla, postgres2 .. but it will
> reject postgrex eg.
If the password mechanism uses standard crypt() passwords, then only the
first eight letters count. It just happens that postgres is eight
letters long.
This has been a public service educated guess.
--
Sam Barnett-Cormack
Software Devel
alue FROM my_sequence;
where my_sequence is the sequence name.
try:
\d my_sequence; (same substitution as before)
for more info.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancast
Is there any way to change the prompt in the psql CLI client? In the
same way as one does with bash, or other shells.
Ideally I want:
[EMAIL PROTECTED]:database $
as the prompt. Anyone got any ideas/references?
--
Sam Barnett-Cormack
Software Developer | Student of
On Mon, 1 Sep 2003, Renney Thomas wrote:
> Sam Barnett-Cormack wrote:
>
> >Is there any way to change the prompt in the psql CLI client? In the
> >same way as one does with bash, or other shells.
> >
> >Ideally I want:
> >
> >[EMAIL PROTECTED]:database $
tation, but it seems
> that when lots of processes are waiting to update the same row, it
> gets incredibly slow.
All trying to access the same row seems a bad idea generally. Instead,
why not make it store a new record for each instance, and have a cronjob
each day update the statist
rsync logs together using postgres. Works like a charm. You do have
to allow that queries are going to take a long time. I use about 6
queries to summarise a quarter's data - each run for each month, so a
total of 18 queries. These run in a little over 24 hours. And there are
many, many recor
an days I would expect.
However, you may want to ensure that all tuning in postgresql.conf is
correct, as it may not be using all possible resources. That will
probably only make a small difference.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Ma
On Mon, 8 Sep 2003, Vasilis Ventirozos wrote:
> I use the default comfiguration file with the tcpip enabled
> any sagestion about the configuration file ?
Post a copy of it to the list, along with the specs of the machine it is
running on, and I'm sure we'll all pipe in.
s
> #deadlock_timeout = 1000# in milliseconds
> #default_transaction_isolation = 'read committed'
> #max_expr_depth = 1 # min 10
> #max_files_per_process = 1000 # min 25
> #password_encryption = true
> #sql_inheritance = true
> #transform_null_equals = false
> #statement_timeout = 0 # 0 is disabled, in milliseconds
> #db_user_namespace = false
>
>
>
> #
> # Locale settings
> #
> # (initialized by initdb -- may be changed)
> LC_MESSAGES = 'C'
> LC_MONETARY = 'C'
> LC_NUMERIC = 'C'
> LC_TIME = 'C'
>
>
Hope some of that helps.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---(end of broadcast)---
TIP 8: explain analyze is your friend
thing exist these days and do you know where on earth I can
> download it ???
> Many thanks
> john clarke
>
>
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---
sssible for me to write
> data on to two different disks. if so how?
For data redundancy, I reccomend RAID level 0 or 5 - 5 is vastly
superior, if you can afford it.
> 2. Steps needed to recover the data in case of database crash or hardware crash.
Keep regular backups with pg_dump
--
On Mon, 6 Oct 2003, Sam Barnett-Cormack wrote:
> On Mon, 6 Oct 2003, Somasekhar Bangalore wrote:
>
> > Hi ,
> >
> > I am setting up a postgres 7.3 database for a big client.I don't want to lose any
> > data in case of database crash or hardware failure . I need
his case?
>
> regards,
> ivan.
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
>
(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
Sam Barnett-Cormack
Software Developer | Student of Physics
ental design flaw in MS Access at least given
> how it is marketed.
That all said, anyone who tells you MS Access is really an RDBMS should
lose their job...
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirr
-(end of broadcast)---------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>
or a given table name ?
>
> many thanks for your quick help,
> Terry
>
>
>
>
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
; 1.2. Software:
> 1.2.1. OS. (wich versions and patches are required for linux and solaris)
> 1.2.2. Required software packages.
>
> Thank you very much.
>
> Best regards,
>
> JCD.
>
> ---(end of broadcast)-----------
> TIP
ing to 7.4
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---(end of broadcast)---
TIP 7: don't forget to increase
atantly obvious,
> but as it's only really happening on production systems (only place with
> the load and the volume of data) I'm loathe to experiment.
>
> Any help appreciated,
>
> Cheers,
>
> Shane
>
> ---(end of broadcast)
can, when it uses indexscan timing is good.
Only thing I can think of is if storage method had been changed. Not
sure if that would even affect it, or if it could do that by itself.
Just brainstorming.
--
Sam Barnett-Cormack
Software Developer | Student of Physics &
On Thu, 5 Feb 2004, Edoardo Ceccarelli wrote:
> after a VACUUM FULL ANALYZE (1h!!) things are ok
Only a hour - lucky you ;)
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster Uni
des the vacuum analyze? And which vacuum locks the tables?
'full' is an option to vacuum, essentially, as is 'analyze', so there
are four variants (ignoring the freeze option):
vacuum
vacuum analyze
vacuum full
vacuum full analyze
It's all pretty obvious from
e config files ?
>
> I'm using Postgresql 7.2.2.
>
> Thanks in advance.
>
> TOm
>
>
>
> ---(end of broadcast)-------
> TIP 7: don't forget to increase your free space map settings
>
--
Sam Barnett-Cormack
S
Director of Information Technology
> Pisgah Astronomical Research Institute
> 1 PARI Drive
> Rosman, NC 28772
> (828)862-5554
> www.pari.edu
>
>
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free
on to the server was lost. Attempting reset: Failed.
> > !#
> >
>
> Regards,
> Oleg
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow Uni
#
>
>
>
> -----------(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
Sam Barnett-Cormack
Software Developer
On Sat, 6 Mar 2004, Bruno Wolff III wrote:
> On Sat, Mar 06, 2004 at 12:52:33 +,
> Sam Barnett-Cormack <[EMAIL PROTECTED]> wrote:
> > In my perl scripts, I have:
> >
> > use DBI;
> > use DBD:Pg;
> >
> > So maybe this is what you need?
>
&g
uie Kwan
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Sam Barnett-Cormack
Software Developer | Student of Physics &
> way to configure connections so as not to use this file?
> Any thoughts would be appreciated.
> Thank you!
Open access to the whole IP range, but require passwords. I think you
can wrap in SSL as well.
--
Sam Barnett-Cormack
Software Developer | Student of Physics
ables
> recently?
Other question that seems, from my experience, to be relevant - are the
referenced/referencing columns indexed?
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.
ull details of
both tables - constraints, indexes, and field types at least.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---(end of broadcast)--
.
> Version: 6.0.644 / Virus Database: 412 - Release Date: 26/3/2004
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---(end of broadcast)---
ecked by AVG anti-virus system ( http://www.grisoft.com).
> Version: 6.0.644 / Virus Database: 412 - Release Date: 26/3/2004
>
>
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancast
d is only quick if there's an index on the order by
field, and and where clause is faster if it can use an index to speed up
the query. I would say you might want an index on both of them together,
a joint index.
--
Sam Barnett-Cormack
Software Developer | Stude
d to restore my database with this upgrade?
You will need to do a dump/restore, yes - you always do when the second
part of the version number changes.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.a
on table2.table1_id? It is a foreign
> key in a parent/child 1 to many relationship.
>
> Please help me understand what is going on, and what I am missunderstanding
> about foreign keys.
Only slightly, you're creating the wrong way around. You want to be
adding the foreign keep to table2.
--
non-immutable internal function, if
I know that it *is* really immutable, at least for my purposes, and use
a functional index.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster Univers
er you describe. The size on disk of the database is 167GiB. It runs
just fine, and that's on linux on intel hardware.
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
--
es (DB1 &
> DB2) with no problem. It looks like all of the data is intact. Any
> ideas what may be the root of the problem or how I can find out more
> about this error PQputline returned?
Perhaps the other machine has a filesystem limit forbidding such large
files? Various filesys
ining field
has the same name in both tables (which is just good design practice).
table1 NATURAL INNER JOIN table2, for example, is equivalent to table1
INNER JOIN table2 ON (table1.table2_keyfield = table2.table2_keyfield)
Feel free to look in the documentation under 'joins' - it
On Wed, 7 Jul 2004 [EMAIL PROTECTED] wrote:
> Hi,
>
> Could you please send me an advice how to configure Postgres database
>
> With non standard pg_xlog directory location i.e. on other (non db) physical
> disk ???
One word: symlinks.
--
Sam Barnett-Cormack
OT IN ('pg_catalog', 'pg_toast') AND
> > pg_catalog.pg_table_is_visible(c.oid)
> > ORDER BY n.nspname, c.relname;
> > \o
> > \i /tmp/grant.sql
> >
> >
> > The above could be put in a script and run from a Unix command prompt.
t;> ON n.oid = c.relnamespace
> >>WHERE c.relkind IN ('r','v','S') AND
> >> n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
> >> pg_catalog.pg_table_is_visible(c.oid)
> >>OR
62 matches
Mail list logo