Re: question

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 <patna73(at)gmail(dot)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(at)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(at)openscg(dot)com> wrote:
>
>>
>> On Thu, Oct 15, 2015 at 3:55 PM, Guillaume Lelarge <
>> guillaume(at)lelarge(dot)info> wrote:
>>
>>> 2015-10-15 20:40 GMT+02:00 anj patnaik <patna73(at)gmail(dot)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 <melvin6925(at)gmail(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.