4

I have a pair of very large (~17 GB) database dumps that I want to load into postgresql 9.3. After installing the database packages, learning more or less how to use them, and fiddling around a little on various StackExchange pages (particularly this question), it looks like a proper command for me to use is something like:

cat mydb.pgdump | psql mydb

because of the format the dump is in. My machine has 16 GB of RAM, and I'm not familiar with the cat command but I do know that my RAM is 99% exhausted and the database is taking a while to load. My machine isn't non-responsive to the point of hanging; I can run other commands in other terminal windows and have them execute at a reasonable clip, but I am wondering if cat is the best way to pipe in the file or if something else is more efficient? My concern is that maybe cat could be using up all the RAM so the database doesn't have much to work with, throttling its performance. But I'm new to thinking about RAM issues like this and don't know if I'm worrying about nothing.

Now that I think about it, this seems to be more of a question about cat and its memory usage than anything else. If there is a more appropriate forum for this question please let me know. Thanks!

Community
  • 1
RussH
  • 143
  • 1
  • 5
  • 1
    The correct load command is `psql dbname < infile` ([documentation](http://www.postgresql.org/docs/9.1/static/backup-dump.html#BACKUP-DUMP-RESTORE)) – beerbajay Jun 02 '14 at 20:55
  • 2
    Additionally, there is [documentation about large database dumps here.](http://www.postgresql.org/docs/9.1/static/backup-dump.html#BACKUP-DUMP-LARGE) They recommend using the `split` command. – beerbajay Jun 02 '14 at 21:01

1 Answers1

1

My concern is that maybe cat could be using up all the RAM so the database doesn't have much to work with, throttling its performance.

It isn't. cat just streams data from input to output.

It's unnecessary here anyway, use

psql dbname < mydb.pgdump

my RAM is 99% exhausted and the database is taking a while to load.

How are you measuring "99% exhaused"? I'm wondering if you're failing to exclude buffers/cache. Show the output of free -h (or if that fails with an error, free -m).

In general, for a faster DB restore I recommend taking a custom-format (-Fc) dump then using parallel pg_restore instead of psql.

Craig Ringer
  • 10,553
  • 9
  • 38
  • 59