copying a large database to change encoding

[Matthew Chambers]

Would something like this work best, or is it better to use pgdump?

CREATE DATABASE newDatabase TEMPLATE oldDatabase ENCODING 'UTF8'

Does using a template do a file system copy or just SQL copy everything
over?

-Matt

[Adrian Klaver]

Post by Matthew Chambers
Would something like this work best, or is it better to use pgdump?
CREATE DATABASE newDatabase TEMPLATE oldDatabase ENCODING 'UTF8'

I actually just did that for a database.

Post by Matthew Chambers
Does using a template do a file system copy or just SQL copy everything

The caveats are noted here:

http://www.postgresql.org/docs/9.3/interactive/sql-createdatabase.html

"Although it is possible to copy a database other than template1 by
specifying its name as the template, this is not (yet) intended as a
general-purpose "COPY DATABASE" facility. The principal limitation is
that no other sessions can be connected to the template database while
it is being copied. CREATE DATABASE will fail if any other connection
exists when it starts; otherwise, new connections to the template
database are locked out until CREATE DATABASE completes. See Section
21.3 for more information

Post by Matthew Chambers
over?
-Matt

[Matthew Chambers]

Thanks,

How long did it take to complete?

Post by Adrian Klaver

Post by Matthew Chambers
Would something like this work best, or is it better to use pgdump?
CREATE DATABASE newDatabase TEMPLATE oldDatabase ENCODING 'UTF8'

I actually just did that for a database.

Post by Matthew Chambers
Does using a template do a file system copy or just SQL copy everything

http://www.postgresql.org/docs/9.3/interactive/sql-createdatabase.html
"Although it is possible to copy a database other than template1 by
specifying its name as the template, this is not (yet) intended as a
general-purpose "COPY DATABASE" facility. The principal limitation is
that no other sessions can be connected to the template database while
it is being copied. CREATE DATABASE will fail if any other connection
exists when it starts; otherwise, new connections to the template
database are locked out until CREATE DATABASE completes. See Section
21.3 for more information

Post by Matthew Chambers
over?
-Matt

[Adrian Klaver]

Post by Matthew Chambers
Thanks,
How long did it take to complete?

I did not time that copy. I just tried with a small database(76 MB on
disk) and it took 27 secs. This is using Linux on an older(3 yrs+) i386
machine.

[Vick Khera]

It should take about as much time as cp -r of the directory for that
DB would take.


On Tue, Oct 14, 2014 at 1:58 PM, Adrian Klaver

Post by Matthew Chambers
Thanks,
How long did it take to complete?

I did not time that copy. I just tried with a small database(76 MB on disk)
and it took 27 secs. This is using Linux on an older(3 yrs+) i386 machine.
--
Adrian Klaver
--
http://www.postgresql.org/mailpref/pgsql-general

[Albe Laurenz]

Post by Matthew Chambers
Would something like this work best, or is it better to use pgdump?
CREATE DATABASE newDatabase TEMPLATE oldDatabase ENCODING 'UTF8'
Does using a template do a file system copy or just SQL copy everything over?

Using the old database as template will not change the encoding,
so I think that will not work at all.

The documentation says:
The encoding and locale settings must match those of the template database,
except when template0 is used as template.

Did you try it?

I think you will have to dump/restore.

Yours,
Laurenz Albe