Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dump all the indexes/constraints/roles

Dump all the indexes/constraints/roles

From
Patrick B
Date:
Hi guys,

I need to export an entire database to another server, for testing purpose.

Is there any way to export all indexes and constraints ?
Postgres 9.2
Patrick

Re: Dump all the indexes/constraints/roles

From
John R Pierce
Date:
On 10/17/2016 8:32 PM, Patrick B wrote:
> I need to export an entire database to another server, for testing
> purpose.
>
> Is there any way to export all indexes and constraints ?
> Postgres 9.2

on the existing machine, as the postgres user....

pg_dump -Fc -d databasename -f filename.pgdump

then, on the new machine,

createdb newdatabase -o dbownername
pg_restore -d newdatabase filename.pgdump



--
john r pierce, recycling bits in santa cruz

Re: Dump all the indexes/constraints/roles

From
Adrian Klaver
Date:
On 10/17/2016 08:32 PM, Patrick B wrote:
> Hi guys,
>
> I need to export an entire database to another server, for testing purpose.
>
> Is there any way to export all indexes and constraints ?

pg_dump the_database

> Postgres 9.2
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Dump all the indexes/constraints/roles

From
Condor
Date:
On 18-10-2016 06:32, Patrick B wrote:
> Hi guys,
>
> I need to export an entire database to another server, for testing
> purpose.
>
> Is there any way to export all indexes and constraints ?
> Postgres 9.2
> Patrick


Hello,

pg_dump database is that you need, but if you asking do you can export
data + indexes like binary data of indexes to can
you restore data fast without to wait all indexes to be create / rebuild
on another server answer is: NO, you can't export them.


Regards,
Hristo S

Re: Dump all the indexes/constraints/roles

From
Jeff Janes
Date:
On Mon, Oct 17, 2016 at 8:32 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,

I need to export an entire database to another server, for testing purpose.

Is there any way to export all indexes and constraints ?
Postgres 9.2
Patrick

By default pg_dump will export the constraints.  It will export the definition of the indexes, but not the physical contents of them, so they have to be recreated upon import.  That can be slow depending on the sizes and types involved.

Or you can clone the whole thing with pg_basebackup.  (If you have multiple databases on the same cluster, this will take all of them.  The only thing you can do about that is drop the ones you don't want.)

Re: Dump all the indexes/constraints/roles

From
Patrick B
Date:
Thank you guys... good to know that pg_dump does all the job for me :)

So.. If I only dump using the --schema-only option, it will dump all the schemas, constraints, indexes and tables?

Because probably, I'll have to import the data manually. NOt in a single pg_restore I mean. (AWS issue) 

Re: Dump all the indexes/constraints/roles

From
Melvin Davidson
Date:


On Tue, Oct 18, 2016 at 3:11 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Thank you guys... good to know that pg_dump does all the job for me :)

So.. If I only dump using the --schema-only option, it will dump all the schemas, constraints, indexes and tables?

Because probably, I'll have to import the data manually. NOt in a single pg_restore I mean. (AWS issue) 

>...If I only dump using the --schema-only option, it will dump all the schemas, constraints, indexes and tables?

Yes! Please RTFM, it's there for a reason. ---> https://www.postgresql.org/docs/9.5/static/app-pgdump.html

Also, please note you can use -a to only dump data

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.