Обсуждение: Distributing PostGres database to various customers

All,

I am new to PostGres 8 (using 8.2.4 windows version).  We have for several years been using iAnywhere's SQL Anywhere product with our commercial transportation software.  With ASA there are 2 files that must be distributed for the database, a filename.db and a filename.log.  When we do a new installation we normally try and preload the database with data used for lookups, some registration data and if a customer is moving from another software where we've been contracted to convert their old data to our system we preload that.  Once that is done we can distribute the database as part of the setup process.

How can we do this with PostGres?  Other than backup and restore or creating SQL scripts I haven't been able to find another method.  Some of these tables may have over a million rows in them initially if we convert old data.

Best Regards,

Michael Gould
All Coast Intermodal Services, Inc.
904-376-7030

On 6/11/07, Mike Gould <mgould@allcoast.net> wrote:
> How can we do this with PostGres?  Other than backup and restore or creating
> SQL scripts I haven't been able to find another method.  Some of these
> tables may have over a million rows in them initially if we convert old
> data.

The most portable way is probably pg_dump as plaintext:

  pg_dump -Fp mydatabase >mydatabase.dump

Then restore with:

  cat mydatabase.dump | psql mydatabase

Alexander.

Mike Gould wrote:
> All,
>
> I am new to PostGres 8 (using 8.2.4 windows version).  We have for
> several years been using iAnywhere's SQL Anywhere product with our
> commercial transportation software.  With ASA there are 2 files that
> must be distributed for the database, a filename.db and a filename.log.
> When we do a new installation we normally try and preload the database
> with data used for lookups, some registration data and if a customer is
> moving from another software where we've been contracted to convert
> their old data to our system we preload that.  Once that is done we can
> distribute the database as part of the setup process.
>
> How can we do this with PostGres?  Other than backup and restore or
> creating SQL scripts I haven't been able to find another method.  Some
> of these tables may have over a million rows in them initially if we
> convert old data.

I would give backup/restore a try.  A million rows isn't very big in
postgres terms.  On relatively low-end hardware, I am routinely able to
backup about 300Gb in about 35 databases in under 3 hours.

Sean

On Mon, Jun 11, 2007 at 03:08:07PM +0200, Alexander Staubo wrote:
> On 6/11/07, Mike Gould <mgould@allcoast.net> wrote:
> >How can we do this with PostGres?  Other than backup and restore or
> >creating
> >SQL scripts I haven't been able to find another method.  Some of these
> >tables may have over a million rows in them initially if we convert old
> >data.
>
> The most portable way is probably pg_dump as plaintext:
>
>  pg_dump -Fp mydatabase >mydatabase.dump
>
> Then restore with:
>
>  cat mydatabase.dump | psql mydatabase

This is better phrased:

    psql -f mydadatabase.dump

If anything goes wrong, the error will contain the line number where
it went wrong.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate