copying a large database to change encoding

On 10/14/2014 10:31 AM, Matthew Chambers wrote:

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.

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

over?

-Matt

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Thanks,

How long did it take to complete?

On 15/10/14 06:39, Adrian Klaver wrote:

On 10/14/2014 10:31 AM, Matthew Chambers wrote:

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.

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

over?

-Matt

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

On 10/14/2014 10:43 AM, Matthew Chambers wrote:

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
adrian.klaver@aklaver.com

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

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
<adrian.klaver@aklaver.com> wrote:

On 10/14/2014 10:43 AM, Matthew Chambers wrote:

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
adrian.klaver@aklaver.com

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

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

Matthew Chambers wrote:

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

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