Обсуждение: Clearing tables questions
Hi, I have two fairly simple questions (I think) but I can't seem to find the answer in the docs or mailing-lists. I'm using pg_dump and pg_restore to backup my dbase. However, I noticed that when I do a pg_restore, the dumped contents are added to the currents ones. This is not what I want. The tables should be cleared first. How do I do this initializing the dbase? Another question I have is the following. What sql command can I use to clear (delete) all tables not starting with 'pg_'. I can easily come up with a query that gives me all tables with the specified prefix. But when I try to 'loop' using a cursor I run into all kind of syntax problems. I'd be very happy if you can help me with any of these questions. thanx in advance, Joep.
Joep DeVocht <joep@masktools.com> writes: > Hi, > > I have two fairly simple questions (I think) but I can't > seem to find the answer in the docs or mailing-lists. > > I'm using pg_dump and pg_restore to backup my dbase. > However, I noticed that when I do a pg_restore, the > dumped contents are added to the currents ones. > This is not what I want. The tables should be cleared first. > How do I do this initializing the dbase? pg_restore assumes the database is completely empty. If it's not you may get duplicate information. > Another question I have is the following. > What sql command can I use to clear (delete) all tables not > starting with 'pg_'. Easiest thing to do is probably: 1. use 'pg_dump -s' to save the schema 2. DROP DATABASE 3. CREATE DATABASE 4. psql mydb < schema_dump.sql -Doug