Thread: Copying a database
Good day all, As a relative newbie to Postgres, I am looking for the "best way" to make a copy of a database I am using. Is there a "standard way" to use pgAdmin to do this? I've seen a number of posts using the command line to do this, but so far, about all I've seen using pgAdmin are posts about using the backup/restore routine to do this, but people seem to have trouble with this at times. All I'm interested in copying is the database structure at this point, and I think I can see in pgAdmin how to do this with the backup. So, would the steps be..... 1. Create the database backup. 2. Create a new, empty database with a new name. 3. Restore the backup to the new, empty database? Thanks for any guidance you can provide, and have a good day. Ron
On 8/12/10 4:06 PM, Ron Gafron wrote:
>
> All I'm interested in copying is the database structure at this point,
> and I think I can see in pgAdmin how to do this with the backup. So,
> would the steps be.....
> 1. Create the database backup.
> 2. Create a new, empty database with a new name.
> 3. Restore the backup to the new, empty database?
That's pretty much it, yes.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
Le 13/08/2010 01:06, Ron Gafron a écrit : > Good day all, > > As a relative newbie to Postgres, I am looking for the "best way" to > make a copy of a database I am using. Is there a "standard way" to use > pgAdmin to do this? I've seen a number of posts using the command line > to do this, but so far, about all I've seen using pgAdmin are posts > about using the backup/restore routine to do this, but people seem to > have trouble with this at times. > > All I'm interested in copying is the database structure at this point, > and I think I can see in pgAdmin how to do this with the backup. So, > would the steps be..... > 1. Create the database backup. > 2. Create a new, empty database with a new name. > 3. Restore the backup to the new, empty database? > > Thanks for any guidance you can provide, and have a good day. > There is two possible ways to save and restore the structure only. If you want a custom dump, you'll have to dump all the database (structure and data). It's on the restore step that you need to tick the "Only schema" checkbox. If you want a plain (SQL) dump, you have to tick the "Only schema" checkbox before doing the dump. You don't have anything special to on the restore step. /me wondering why we don't propose to do a "schema only" and "data only" dump when using a custom or tar dump. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On 13/08/2010 06:32, Guillaume Lelarge wrote: > If you want a plain (SQL) dump, you have to tick the "Only schema" > checkbox before doing the dump. You don't have anything special to on > the restore step. It may be worth adding to this that you need to restore a plain dump via psql on the command line: psql -U <username> -f <sql file name> <database name> There has been confusion in the past with people trying to use the "Restore" command in pgAdmin with plain dumps - this uses pg_restore, which is intended for custom dumps only. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Le 13/08/2010 11:38, Raymond O'Donnell a écrit : > On 13/08/2010 06:32, Guillaume Lelarge wrote: > >> If you want a plain (SQL) dump, you have to tick the "Only schema" >> checkbox before doing the dump. You don't have anything special to on >> the restore step. > > It may be worth adding to this that you need to restore a plain dump via > psql on the command line: > > psql -U <username> -f <sql file name> <database name> > > There has been confusion in the past with people trying to use the > "Restore" command in pgAdmin with plain dumps - this uses pg_restore, > which is intended for custom dumps only. > Oops, sure, you're right. I completely forgot that one. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
Thanks for the information. Much appreciated. Ron On 8/13/2010 4:55 AM, Guillaume Lelarge wrote: > Le 13/08/2010 11:38, Raymond O'Donnell a écrit : >> On 13/08/2010 06:32, Guillaume Lelarge wrote: >> >>> If you want a plain (SQL) dump, you have to tick the "Only schema" >>> checkbox before doing the dump. You don't have anything special to on >>> the restore step. >> It may be worth adding to this that you need to restore a plain dump via >> psql on the command line: >> >> psql -U<username> -f<sql file name> <database name> >> >> There has been confusion in the past with people trying to use the >> "Restore" command in pgAdmin with plain dumps - this uses pg_restore, >> which is intended for custom dumps only. >> > Oops, sure, you're right. I completely forgot that one. > >