Re: [GENERAL] Dumping/Restoring with constraints?

"Phoenix Kiula" writes:
> This is the dump command being used on a 8.2.3 database on Linux:

> $ pg_dumpall > mydb.sql
> $ umask 077
> $ gzip mydb.sql

> Then I download the mydb.sql.gz file into my local computer (Mac OSX
> with 8.3.3) and unzip it to mydb.sql.

That's all fine ...

> The local database is already created, with all tables and constraints and all.

.... but this isn't. A pg_dump script expects to restore into an empty
database. For pg_dumpall, you shouldn't even have created the
databases, just start from a virgin installation.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Aug 27, 2008 at 10:42 PM, Tom Lane wrote:


> For pg_dumpall, you shouldn't even have created the
> databases, just start from a virgin installation.



Should I have CREATEd the Database at least without the table structure?

I dropped the database locally. Entirely. Gone.

Then I tried this:

$ pg_restore -h localhost mydb.sql

This tells me:

pg_restore: [archiver] input file does not appear to be a valid archive

What gives?

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Phoenix Kiula" writes:
> Then I tried this:
> $ pg_restore -h localhost mydb.sql
> This tells me:
> pg_restore: [archiver] input file does not appear to be a valid archive

No, you were right the first time: just feed the dump script to psql.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ok. Here is what it is.

OLD Database:
Since 2002. May contain non-UTF8 data. But I thought I had modified it
all when I changed it to UTF-8 pgsql database (it was originally
Mysql). The database works very well on a very busy website.
Everything on that website is now UTF-8. I wish to mirror this
database locally on my home machine. Server is linux with 6GB ram and
pgsql 8.2.3, home machine is osx with imac of 4GM ram and 8.3.3 (new
pg).

NEW Database:
Just installed fresh. There's nothing in it. I can basically wipe all
data out.

WHAT I AM DOING:
1. On the server, I am executing "pg_dumpall > mydb.out". Simple.
2. FTP that mydb.out file to local home machine.
3. Here, locally, I do this: "psql -h localhost -d postgres -U
postgres -f mydb.out". This is what gives me the error.

MY QUESTION:
What can I do to:
(a) Make sure all data on pgsql on server (OLD Database) is utf-8. Is there
a command I can execute to convert all data? I thought I had converted
it all to utf-8 using PHP sometime ago, which went through each and
every row and column!
(b) Once that data is utf8-ed, how can I bring it home and have a
mirror of the Database.


Thanks.

--
Sent via pgsql-general mailing list (pgsql-general-at-postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general