Re: question

On 10/15/2015 01:35 PM, anj patnaik wrote:
> Hello all,
> I will experiment with -Fc (custom). The file is already growing very large.
>
> I am running this:
> ./pg_dump -t RECORDER  -Fc postgres |  gzip > /tmp/dump
>
> Are there any other options for large tables to run faster and occupy
> less disk space?

Yes, do not double compress. -Fc already compresses the file.

This information and a lot more can be found here:

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html

>
> Below is memory info:
>
> [root@onxl5179 tmp]# cat /proc/meminfo
> MemTotal:       16333720 kB
> MemFree:          187736 kB
> Buffers:           79696 kB
> Cached:         11176616 kB
> SwapCached:         2024 kB
> Active:         11028784 kB
> Inactive:        4561616 kB
> Active(anon):    3839656 kB
> Inactive(anon):   642416 kB
> Active(file):    7189128 kB
> Inactive(file):  3919200 kB
> Unevictable:           0 kB
> Mlocked:               0 kB
> SwapTotal:      33456120 kB
> SwapFree:       33428960 kB
> Dirty:             33892 kB
> Writeback:             0 kB
> AnonPages:       4332408 kB
> Mapped:           201388 kB
> Shmem:            147980 kB
> Slab:             365380 kB
> SReclaimable:     296732 kB
> SUnreclaim:        68648 kB
> KernelStack:        5888 kB
> PageTables:        37720 kB
> NFS_Unstable:          0 kB
> Bounce:                0 kB
> WritebackTmp:          0 kB
> CommitLimit:    41622980 kB
> Committed_AS:    7148392 kB
> VmallocTotal:   34359738367 kB
> VmallocUsed:      179848 kB
> VmallocChunk:   34359548476 kB
> HardwareCorrupted:     0 kB
> AnonHugePages:   3950592 kB
> HugePages_Total:       0
> HugePages_Free:        0
> HugePages_Rsvd:        0
> HugePages_Surp:        0
> Hugepagesize:       2048 kB
> DirectMap4k:       10240 kB
> DirectMap2M:    16766976 kB
>
>
> # CPUs=8
> RHEL 6.5
>
> The PG shared memory info is the defaults as I've not touched the .conf
> file. I am not a DBA, just a test tools developer who needs to backup
> the table efficiently. I am fairly new to PG and not an expert at Linux.
>
> Also if there are recommended backup scripts/cron that you recommend,
> please point them to me.
>
> Thanks!!
>
> On Thu, Oct 15, 2015 at 3:59 PM, Scott Mead <scottm@openscg.com
> <mailto:scottm@openscg.com>> wrote:
>
>
>     On Thu, Oct 15, 2015 at 3:55 PM, Guillaume Lelarge
>     <guillaume@lelarge.info <mailto:guillaume@lelarge.info>> wrote:
>
>         2015-10-15 20:40 GMT+02:00 anj patnaik <patna73@gmail.com
>         <mailto:patna73@gmail.com>>:
>
>             It's a Linux machine with 8 CPUs. I don't have the other
>             details.
>
>             I get archive member too large for tar format.
>
>             Is there a recommended command/options when dealing with
>             very large tables, aka 150K rows and half of the rows have
>             data being inserted with 22MB?
>
>
>         Don't use tar format? I never understood the interest on this
>         one. You should better use the custom method.
>
>
>     + 1
>
>       Use -F c
>
>
>     --
>     Scott Mead
>     Sr. Architect
>     /OpenSCG/
>     PostgreSQL, Java & Linux Experts
>
>
>     http://openscg.com <http://openscg.com/>
>
>
>             -bash-4.1$ ./pg_dump -t RECORDER postgres --format=t -w  >
>             /tmp/dump
>             pg_dump: [archiver (db)] connection to database "postgres"
>             failed: fe_sendauth: no password supplied
>             -bash-4.1$ ./pg_dump -t RECORDER postgres --format=t   >
>             /tmp/dump
>             Password:
>             pg_dump: [tar archiver] archive member too large for tar format
>             -bash-4.1$ pg_dumpall | gzip > \tmp\db.out-`date
>             +\%Y\%m\%d\%H`.gz
>             -bash: pg_dumpall: command not found
>             -bash: tmpdb.out-2015101510 <tel:2015101510>.gz: Permission
>             denied
>             -bash-4.1$ ./pg_dumpall | gzip > \tmp\db.out-`date
>             +\%Y\%m\%d\%H`.gz
>
>
>             Thank you so much for replying and accepting my post to this NG.
>
>             On Thu, Oct 15, 2015 at 11:17 AM, Melvin Davidson
>             <melvin6925@gmail.com <mailto:melvin6925@gmail.com>> wrote:
>
>                 In addition to exactly what you mean by "a long time" to
>                 pg_dump 77k of your table,
>
>                 What is your O/S and how much memory is on your system?
>                 How many CPU's are in your system?
>                 Also, what is your hard disk configuration?
>                 What other applications are running simultaneously with
>                 pg_dump?
>                 What is the value of shared_memory &
>                 maintenance_work_mem in postgresql.conf?
>
>                 On Thu, Oct 15, 2015 at 11:04 AM, Adrian Klaver
>                 <adrian.klaver@aklaver.com
>                 <mailto:adrian.klaver@aklaver.com>> wrote:
>
>                     On 10/14/2015 06:39 PM, anj patnaik wrote:
>
>                         Hello,
>
>                         I recently downloaded postgres 9.4 and I have a
>                         client application that
>                         runs in Tcl that inserts to the db and fetches
>                         records.
>
>                         For the majority of the time, the app will
>                         connect to the server to do
>                         insert/fetch.
>
>                         For occasional use, we want to remove the
>                         requirement to have a server
>                         db and just have the application retrieve data
>                         from a local file.
>
>                         I know I can use pg_dump to export the tables.
>                         The questions are:
>
>                         1) is there an in-memory db instance or file
>                         based I can create that is
>                         loaded with the dump file? This way the app code
>                         doesn't have to change.
>
>
>                     No.
>
>
>                         2) does pg support embedded db?
>
>
>                     No.
>
>                         3) Or is my best option to convert the dump to
>                         sqlite and the import the
>                         sqlite and have the app read that embedded db.
>
>
>                     Sqlite tends to follow Postgres conventions, so you
>                     might be able to use the pg_dump output directly if
>                     you use --inserts or --column-inserts:
>
>                     http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
>
>
>                         Finally, I am noticing pg_dump takes a lot of
>                         time to create a dump of
>                         my table. right now, the table  has 77K rows.
>                         Are there any ways to
>                         create automated batch files to create dumps
>                         overnight and do so quickly?
>
>
>                     Define long time.
>
>                     What is the pg_dump command you are using?
>
>                     Sure use a cron job.
>
>
>                         Thanks for your inputs!
>
>
>
>                     --
>                     Adrian Klaver
>                     adrian.klaver@aklaver.com
>                     <mailto:adrian.klaver@aklaver.com>
>
>
>                     --
>                     Sent via pgsql-general mailing list
>                     (pgsql-general@postgresql.org
>                     <mailto:pgsql-general@postgresql.org>)
>                     To make changes to your subscription:
>                     http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
>                 --
>                 *Melvin Davidson*
>                 I reserve the right to fantasize.  Whether or not you
>                 wish to share my fantasy is entirely up to you.
>
>
>
>
>
>         --
>         Guillaume.
>         http://blog.guillaume.lelarge.info
>         http://www.dalibo.com
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com