The PostgreSQL default configuration is very conservative so as to insure it will work on almost any system. However, based on your latest information, you should definitely adjust shared_buffers = 4GB maintenance_work_mem = 512MB
Note that you will need to restart PostgreSQL for this to take effect. On Thu, Oct 15, 2015 at 4:35 PM, anj patnaik <patn...@gmail.com> 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? > > 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 <sco...@openscg.com> wrote: > >> >> On Thu, Oct 15, 2015 at 3:55 PM, Guillaume Lelarge < >> guilla...@lelarge.info> wrote: >> >>> 2015-10-15 20:40 GMT+02:00 anj patnaik <patn...@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 >> >> >>> >>> >>>> -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.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 <melvin6...@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.kla...@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.kla...@aklaver.com >>>>>> >>>>>> >>>>>> -- >>>>>> Sent via pgsql-general mailing list (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 >>> >> >> > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
