Re: does pg_dump get delayed if I have a loop that does continuous insertions - Mailing list pgsql-general

From Adrian Klaver
Subject Re: does pg_dump get delayed if I have a loop that does continuous insertions
Date
Msg-id 56315E91.7030804@aklaver.com
Whole thread Raw
In response to Re: does pg_dump get delayed if I have a loop that does continuous insertions  (anj patnaik <patna73@gmail.com>)
List pgsql-general
On 10/28/2015 01:09 PM, anj patnaik wrote:
> I was trying to achieve smallest file possible so tried the xz. Right
> now the db size returned from SELECT
> pg_size_pretty(pg_database_size('postgres') is 1.4 GB and the size of
> the dump with xz is 2.2 GB.

Probably not a good idea to put your data in the postgres database. This
is one of the system databases created by initdb and is meant to be used
as a default database to connect to, not really as one to store data in.
Just did a test with pg_dumpall and it is not dumped when you backup the
entire cluster.

Also hard to compare the numbers without seeing the command that
produced the database dump. An example from here on a small database:

test=> select pg_size_pretty(pg_database_size('production'));
  pg_size_pretty
----------------
  53 MB

aklaver@panda:~> pg_dump -Fc -d production -U postgres -f prod.out

aklaver@panda:~> l -h prod.out
-rw-r--r-- 1 aklaver users 5.4M Oct 28 16:44 prod.out

>
> Is there a limit to the size of the database that pg_dump will run on?
> Will it work when db is in TBs?
>
> Also, I want to know if anyone has found any handy cron scripts for
> automated backups to run on a daily/weekly basis? i found some on
> google, but interested to know if there are better ones.
>
> Thanks,
> ap
>
> On Wed, Oct 28, 2015 at 12:05 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/27/2015 04:10 PM, anj patnaik wrote:
>
>         I am running pg_dump on a database while on another machine
>         running a
>         loop to do insertions.
>
>         Does pg_dump wait for idle activity before it completes or how
>         does it
>         determine when it has all the records needed for archiving?
>
>
>     http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
>
>     "pg_dump is a utility for backing up a PostgreSQL database. It makes
>     consistent backups even if the database is being used concurrently.
>     pg_dump does not block other users accessing the database (readers
>     or writers).
>
>
>         I am using the compressed mode and also using xz.
>
>
>     Again, why?
>
>
>         Thanks,
>         ap
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Postgresql Installation -- Red Hat vs OpenSUSE vs Ubuntu
Next
From: Yves Dorfsman
Date:
Subject: Re: does pg_dump get delayed if I have a loop that does continuous insertions