[GENERAL] Backup & Restore
Hi, I have a simple question that I can't find the answer to in the documentation: how do I backup & restore a single database, ie. when going from one cluster to another? I'm fairly comfortable with the command line so I can create and restore backups but things do not always work as expected. pg_dump -U postgres -f my_database.dmp -F c my_database now I want to restore this database to another system where a database with the same name already exists (from production to development): According to the documentation the options -c and -a can be set on pg_restore ie., pg_restore -U postgres -c -d psytec psytec.dmp should drop all the tables before recreating them but I get a load of errors: pg_restore: [archiver (db)] Error from TOC entry 1949; 2620 297386 TRIGGER RI_ConstraintTrigger_297386 postgres using -a to restore data only means fewer errors as pg_restore exits on the first error Is the only way to drop the target database and recreate an empty one? Or am I simply misreading the docs? Thanks very much for any help. Charlie -- Charlie Clark Helmholtzstr. 20 Düsseldorf D- 40215 Tel: +49-211-938-5360 GSM: +49-178-782-6226 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How do I change sort order behavious with nulls
On Sat, 19 Feb 2005 12:01:07 -0600, Bruno Wolff III <[EMAIL PROTECTED]> wrote: On Sat, Feb 19, 2005 at 18:04:32 +0100, charlie clark <[EMAIL PROTECTED]> wrote: Dear list, is there a simple way to change the way ORDER BY works on columns with NULLs? I can understand the need for default behaviour but there must be cases when this is undesirable. I have such a query with the NULLs arising as the result of an OUTER JOIN and I would like to ORDER BY DESC with NULLs treated as <= 0. I've already tried a few things but nothing's working so far. Presumably what you mean is that you want NULLs to be output last when doing a descending order by. You can do this using ORDER BY whatever IS NULL ASC, whatever DESC . If you really mean you want to treat them as less than or equal to 0, then you can pick such a value and use coalesce to change NULLs to that value in the ORDER BY clause. Yes, this is what I want to do. It seems COALESCE is the clearest way to do this. SELECT COALESCE(mydate, timestamp'-01-01') AS mydate FROM mytable ORDER BY date DESC There seems to be no penalty involved in running this as well. Thank you very much Charlie -- Charlie Clark Helmholtzstr. 20 Düsseldorf ---(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
[GENERAL] Weird behaviour on a join with multiple keys
Hi,
I'm getting unexpected results on a query which involves joining two
tables on two common variables (firstname and lastname).
This is the basic query:
SELECT table1.lastname, table1.firstname
FROM table1
INNER JOIN table2 ON
(table2.name = table1.name
AND
table2.vorname = table1.vorname)
This is returning many rows fewer than I expect and is ignoring a lot
where table1.firstname = table2.firstname AND table1.lastname =
table2.lastname. In fact when I extend the query by a WHERE clause
such as WHERE table1.lastname IS LIKE 'Sch%' I get results which are
not returned by the original query.
I'm not very au fait with the inner workings of PostgreSQL but
EXPLAIN does not seem, to me at least, to provide an explanation for
the missing results.
"Merge Join (cost=1987.97..2121.24 rows=34 width=22)"
" Merge Cond: (("outer"."?column3?" = "inner"."?column3?") AND
("outer"."?column4?" = "inner"."?column4?"))"
" -> Sort (cost=364.97..375.99 rows=4409 width=22)"
"Sort Key: (table1.lastname)::text, (table1.firstname)::text"
"-> Seq Scan on table1 (cost=0.00..98.09 rows=4409 width=22)"
" -> Sort (cost=1623.00..1667.00 rows=17599 width=21)"
"Sort Key: (table2.lastname)::text, (table2.firstname)::text"
"-> Seq Scan on table2 (cost=0.00..381.99 rows=17599 width=21)"
Am I missing something big and obvious here?
Charlie
--
Charlie Clark
Helmholtzstr. 20
Düsseldorf
D- 40215
Tel: +49-211-938-5360
GSM: +49-178-782-6226
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Weird behaviour on a join with multiple keys
Am 09.03.2007 um 05:30 schrieb Tom Lane: Charlie Clark <[EMAIL PROTECTED]> writes: I'm getting unexpected results on a query which involves joining two tables on two common variables (firstname and lastname). That looks like it should work. Given that you describe the columns as "names" I'm supposing they are of textual datatypes. Maybe you have a messed-up encoding or locale situation that is causing the sorts to not work properly? What PG version is this exactly, on what platform, and what do "show lc_collate" and "show server_encoding" say? I'm running PostgreSQL 8.1.4 on Mac OS X psytec=# show lc_collate; lc_collate - de_DE.UTF-8 (1 row) psytec=# show server_encoding; server_encoding - LATIN1 (1 row) I thought that it might be something to do with the encoding - one of the tables has just been imported and I had some "fun" doing that but it "looks" okay now. Is there a way of checking? Charlie -- Charlie Clark Helmholtzstr. 20 Düsseldorf D- 40215 Tel: +49-211-938-5360 GSM: +49-178-782-6226 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Weird behaviour on a join with multiple keys
Am 09.03.2007 um 16:15 schrieb Tom Lane: psytec=# show lc_collate; lc_collate - de_DE.UTF-8 (1 row) psytec=# show server_encoding; server_encoding - LATIN1 (1 row) There's your problem right there. The string comparison routines are built on strcoll(), which is going to expect UTF8-encoded data because of the LC_COLLATE setting. If there are any high-bit-set LATIN1 characters in the database, they will most likely look like invalid encoding to strcoll(), and on most platforms that causes it to behave very oddly. You need to keep lc_collate (and lc_ctype) in sync with server_encoding. That does indeed seem to have been the problem even though the examples I was looking at were all using plain ASCII characters. Glad to know it wasn't a bug and to have learned something new. Charlie -- Charlie Clark Helmholtzstr. 20 Düsseldorf D- 40215 Tel: +49-211-938-5360 GSM: +49-178-782-6226 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] How do I change sort order behavious with nulls
Dear list, is there a simple way to change the way ORDER BY works on columns with NULLs? I can understand the need for default behaviour but there must be cases when this is undesirable. I have such a query with the NULLs arising as the result of an OUTER JOIN and I would like to ORDER BY DESC with NULLs treated as <= 0. I've already tried a few things but nothing's working so far. Thanx for any pointers. Charlie -- Charlie Clark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Backuping and restoring databases on different systems
Dear list, I've got an application using PostgreSQL which is due to be moved servers. I've been able to backup on the old and restore on the new server but only once and as the current application is regularly being updated I'd like to be able to do this repeatedly using the "clean" option. I think this is something to do with the rights I have on the new system and would like to know what I need to ask for from my ISP in order to get things to work properly. My ISP seems to have less PostgreSQL experience than I do. I've created a backup like this pg_backup -f charlie.dmp -F c charlie I've been able to restore on my development machine without any problems pg_restore -d test -c -O charlie.dmp (I have been given database and user names on the target machine than on the current one). but I get CONSTRAINT errors when I try this on the target machine unless I manually DROP all TABLES and SEQUENCES in the database. Having looked at the code generated it seems that I am missing the rights to DROP my own SCHEMA (called public) which would be inline with the fact that I am not able to delete my own database. Is it possible that I'm over looking a simple command switch or are there other ways of doing what I want: drop the existing schema, import the backup, or do I need exta permissions for my user? Thank you very much. Charlie Clark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
