Thread: copying a large database to change encoding
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
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
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 > >
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
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
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