Groups > comp.databases.postgresql > #757

Re: Issues with backing up Postgres by copying folder

From	DFS <nospam@dfs.com>
Subject	Re: Issues with backing up Postgres by copying folder
Newsgroups	comp.databases.postgresql
References	<smBIA.407$xR7.221@fx28.iad> <20170419201146.02a1844344207bc63a19edcf@shallow.house> <HNzMA.23981$So6.1424@fx17.iad> <20170502000731.7d5f6e4492d851c4800bd500@shallow.house>
Message-ID	<tS1OA.59756$So6.15017@fx17.iad> (permalink)
Organization	blocknews - www.blocknews.net
Date	2017-05-02 11:48 -0400

Show all headers | View raw

On 5/1/2017 7:07 PM, Terry Shanks wrote:
> On Fri, 28 Apr 2017 00:45:48 -0400
> DFS <nospam@dfs.com> wrote:
> 
>> What issues do you think I can expect if I restore the data folder from
>> a 9.6.2 'backup' onto a system running a later postgres version?
>>
> 
> This should work with minor versions eg 9.6.1 <> 9.6.2. A tool exists for migrating data dirs between major versions - pg_upgrade (https://www.postgresql.org/docs/9.6/static/pgupgrade.html)

Thanks.

After reading the comments here and the docs on pgdump and filesystem 
backups, I agree with you guys that pg_dump is the way to go.

Do you have any experience with pg_dump and large databases?  Are the 
dumps real slow to create and restore?

The db I'm thinking about is currently a 10GB file in SQLite.  Do you 
consider that large?

1 table  with  1.0M rows
3 tables with  1.5M rows each
1 table  with  9.0M rows
1 table  with 67.0M rows
other smaller ones

Total 82M rows across 17 tables.

Shocking eh?  But it's been robust and is still speedy for SELECTs. 
Adding indexes and doing some other table operations now takes 20 
minutes on the big table.  So I'm gonna upgrade to Postgres.

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-04-15 23:17 -0400
  Re: Issues with backing up Postgres by copying folder Terry Shanks <deep@shallow.house> - 2017-04-19 20:11 +0100
    Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-04-28 00:45 -0400
      Re: Issues with backing up Postgres by copying folder Terry Shanks <deep@shallow.house> - 2017-05-02 00:07 +0100
        Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-05-02 11:48 -0400
          Re: Issues with backing up Postgres by copying folder Dimitri Fontaine <dimitri.fontaine@schibsted.com> - 2017-05-03 09:16 +0200
            Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-05-03 16:07 -0400
            Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-05-20 20:01 -0400
              Re: Issues with backing up Postgres by copying folder Dimitri Fontaine <dimitri.fontaine@schibsted.com> - 2017-05-21 13:46 +0200
                Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-05-21 08:48 -0400
                Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-05-31 07:29 -0400
                Re: Issues with backing up Postgres by copying folder Dimitri Fontaine <dimitri.fontaine@schibsted.com> - 2017-05-31 14:26 +0200
                Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-05-31 09:52 -0400
                Re: Issues with backing up Postgres by copying folder Dimitri Fontaine <dimitri.fontaine@schibsted.com> - 2017-05-31 16:27 +0200
          Re: Issues with backing up Postgres by copying folder Terry Shanks <deep@shallow.house> - 2017-05-03 13:57 +0100
            Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-05-11 11:11 -0400
              Re: Issues with backing up Postgres by copying folder Robert Klemme <shortcutter@googlemail.com> - 2017-05-11 23:08 +0200
    Re: Issues with backing up Postgres by copying folder Gordon Gekko <greed@is.good.invalid> - 2017-05-08 02:39 +0000
  Re: Issues with backing up Postgres by copying folder Rainer Weikusat <rweikusat@talktalk.net> - 2017-04-23 22:15 +0100
    Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-04-28 00:42 -0400
      Re: Issues with backing up Postgres by copying folder Rainer Weikusat <rweikusat@talktalk.net> - 2017-04-28 12:39 +0100
        Re: Issues with backing up Postgres by copying folder DFS <nospam@dfs.com> - 2017-04-28 11:16 -0400
          Re: Issues with backing up Postgres by copying folder Rainer Weikusat <rweikusat@talktalk.net> - 2017-04-28 16:42 +0100
            Re: Issues with backing up Postgres by copying folder Rainer Weikusat <rweikusat@talktalk.net> - 2017-04-28 16:48 +0100
              Re: Issues with backing up Postgres by copying folder Terry Shanks <deep@shallow.house> - 2017-05-02 00:00 +0100
              Re: Issues with backing up Postgres by copying folder Mladen Gogala <gogala.mladen@gmail.com> - 2017-05-08 02:41 +0000

csiph-web