Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #739 > unrolled thread

Issues with backing up Postgres by copying folder

Back to article view | Back to comp.databases.postgresql

Contents

  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

Page 1 of 2  [1] 2  Next page →

#739 — Issues with backing up Postgres by copying folder

Windows
Postgres 9.6.2

On install I set the /data directory to a data drive (D:\)

How much trouble will it cause if I:

1) backup that entire /data directory by copying it to a backup place
2) reinstall Postgres (and set the /data directory to the same
    location as before)
3) copy over the new /data directory with my backup?

No pgdump - just copy and replace files.

[toc] | [next] | [standalone]

#740

On Sat, 15 Apr 2017 23:17:34 -0400
DFS <nospam@dfs.com> wrote:

> Windows
> Postgres 9.6.2
> 
> On install I set the /data directory to a data drive (D:\)
> 
> How much trouble will it cause if I:
> 
> 1) backup that entire /data directory by copying it to a backup place
> 2) reinstall Postgres (and set the /data directory to the same
>     location as before)
> 3) copy over the new /data directory with my backup?
> 
> No pgdump - just copy and replace files.
> 
> 

This will be fine as long as the server(s) are stopped at the time of copy, 
and they are they running the same version.

[toc] | [prev] | [next] | [standalone]

#746

On 4/19/2017 3:11 PM, Terry Shanks wrote:
> On Sat, 15 Apr 2017 23:17:34 -0400
> DFS <nospam@dfs.com> wrote:
> 
>> Windows
>> Postgres 9.6.2
>>
>> On install I set the /data directory to a data drive (D:\)
>>
>> How much trouble will it cause if I:
>>
>> 1) backup that entire /data directory by copying it to a backup place
>> 2) reinstall Postgres (and set the /data directory to the same
>>      location as before)
>> 3) copy over the new /data directory with my backup?
>>
>> No pgdump - just copy and replace files.
>>
>>
> 
> This will be fine as long as the server(s) are stopped at the time of copy,
> and they are they running the same version.

Thanks.

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?

[toc] | [prev] | [next] | [standalone]

#756

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) 

[toc] | [prev] | [next] | [standalone]

#757

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.

[toc] | [prev] | [next] | [standalone]

#758

DFS <nospam@dfs.com> writes:
> 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.

pgloader can migrate the schema and data over in a single command, so I
think you should have a look in case that's useful to you:

  http://pgloader.io
  pgloader ./test/sqlite/sqlite.db postgresql://user@host/newdb

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

[toc] | [prev] | [next] | [standalone]

#760

On 5/3/2017 3:16 AM, Dimitri Fontaine wrote:
> DFS <nospam@dfs.com> writes:
>> 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.
> 
> pgloader can migrate the schema and data over in a single command, so I
> think you should have a look in case that's useful to you:
> 
>    http://pgloader.io
>    pgloader ./test/sqlite/sqlite.db postgresql://user@host/newdb
> 
> Regards,


I'll look into that one, thanks.

To learn Postgres I've been building and loading one table at a time.

'SERIAL' was new to me.
the COPY utility was handy for populating tables
psql is really powerful
pgAdmin4 v1.3 is /ridiculously/ slow on Windows

[toc] | [prev] | [next] | [standalone]

#779

On 5/3/2017 3:16 AM, Dimitri Fontaine wrote:
> DFS <nospam@dfs.com> writes:
>> 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.
> 
> pgloader can migrate the schema and data over in a single command, so I
> think you should have a look in case that's useful to you:
> 
>    http://pgloader.io
>    pgloader ./test/sqlite/sqlite.db postgresql://user@host/newdb
> 
> Regards,


Dimitri,

Two questions:

1) Can I specify one table - or a list of a few specific tables - to 
migrate from SQLite to postgres?

It's not clear from your pgloader docs:
------------------------------------------------------------------
INCLUDING ONLY TABLE NAMES LIKE
Introduce a comma separated list of table name patterns used to limit 
the tables to migrate to a sublist.

Example:
INCLUDING ONLY TABLE NAMES LIKE 'Invoice%'
------------------------------------------------------------------


2) How can I build/run pgloader on Windows (8.1)?

Your docs say "Building for the Windows™ Operating System is easy enough 
and the platform is fully supported."

I have clisp-2.49 installed as part of maxima.


Thanks!

[toc] | [prev] | [next] | [standalone]

#780

DFS <nospam@dfs.com> writes:
> 1) Can I specify one table - or a list of a few specific tables - to migrate
> from SQLite to postgres?

Yes. What happens when you try?

> 2) How can I build/run pgloader on Windows (8.1)?

Yes. You will need either SBCL or Clozure-CL for building pgloader tho,
I've not tried it with clisp (it might work but I won't be able to help
you there).

-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

[toc] | [prev] | [next] | [standalone]

#781

On 5/21/2017 7:46 AM, Dimitri Fontaine wrote:
> DFS <nospam@dfs.com> writes:
>> 1) Can I specify one table - or a list of a few specific tables - to migrate
>> from SQLite to postgres?
> 
> Yes. What happens when you try?


I haven't tried yet, 'cause I don't know how to build it on Windows and 
I don't have access to a Linux system right now.

Is it "INCLUDING ONLY TABLE NAMES IN ('Table1','Table2','Table3')?


>> 2) How can I build/run pgloader on Windows (8.1)?
> 
> Yes. You will need either SBCL or Clozure-CL for building pgloader tho,
> I've not tried it with clisp (it might work but I won't be able to help
> you there).

OK.  But how do I build pgloader on Windows (8.1)?

[toc] | [prev] | [next] | [standalone]

#785

On 5/21/2017 8:48 AM, DFS wrote:
> On 5/21/2017 7:46 AM, Dimitri Fontaine wrote:
>> DFS <nospam@dfs.com> writes:
>>> 1) Can I specify one table - or a list of a few specific tables - to 
>>> migrate
>>> from SQLite to postgres?
>>
>> Yes. What happens when you try?
> 
> 
> I haven't tried yet, 'cause I don't know how to build it on Windows and 
> I don't have access to a Linux system right now.
> 
> Is it "INCLUDING ONLY TABLE NAMES IN ('Table1','Table2','Table3')?

Bump.


>>> 2) How can I build/run pgloader on Windows (8.1)?
>>
>> Yes. You will need either SBCL or Clozure-CL for building pgloader tho,
>> I've not tried it with clisp (it might work but I won't be able to help
>> you there).
> 
> OK.  But how do I build pgloader on Windows (8.1)?

Bump.

[toc] | [prev] | [next] | [standalone]

#786

DFS <nospam@dfs.com> writes:
> Bump.
> Bump.

Have you read any docs, like the project's README on github maybe? or
which ones? why didn't it answer your questions? what did you try? what
happened when you tried?

-- 
dim

[toc] | [prev] | [next] | [standalone]

#787

On 5/31/2017 8:26 AM, Dimitri Fontaine wrote:
> DFS <nospam@dfs.com> writes:
>> Bump.
>> Bump.
> 
> Have you read any docs, like the project's README on github maybe? or
> which ones? why didn't it answer your questions? what did you try? what
> happened when you tried?


If your docs covered either question I wouldn't have asked.

[toc] | [prev] | [next] | [standalone]

#788

DFS <nospam@dfs.com> writes:
> If your docs covered either question I wouldn't have asked.

So you've read

  https://github.com/dimitri/pgloader#building-from-sources-on-windows

And followed through

  https://github.com/dimitri/pgloader/issues?utf8=✓&q=label%3A%22Windows%20support%22%20

Are you working with SBCL or CCL? Which problems do you have now when
building the binary for windows? In the normal case it should about just
work, so all I can tell you without specifics is to run `make` and then
you have `./build/bin/pgloader.exe` binary to use…

-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

[toc] | [prev] | [next] | [standalone]

#759

On Tue, 2 May 2017 11:48:32 -0400
DFS <nospam@dfs.com> wrote:
 
> 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?

It took me 4mins to pg_dump a 3GB database just now to give an idea but
of course this depends on many (I/O etc) factors.
10GB is certainly not small for a SQLite database (not heard of any SQLite
DBs that big before). 

> 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.

There is a body of evidence to suggest SQLite is faster than many 
multiuser rdbms' for general use but apparently CREATE INDEX is one 
thing it does fall behind in.

PGLoader recommended by Dimitri sounds excellent and I will check this
out myself too as I have not heard of it before.

[toc] | [prev] | [next] | [standalone]

#765

On 5/3/2017 8:57 AM, Terry Shanks wrote:
> On Tue, 2 May 2017 11:48:32 -0400
> DFS <nospam@dfs.com> wrote:
>   
>> 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?
> 
> It took me 4mins to pg_dump a 3GB database just now to give an idea but
> of course this depends on many (I/O etc) factors.

It just took 30-45 seconds to pg_dump a 3.85GB database on my i5-750 8GB 
RAM system.  And the dump file is 741MB.

3.85GB is what Windows File Explorer reports for the Postgres /data 
directory.  Is there another way to measure the size of a Postgres database?



> 10GB is certainly not small for a SQLite database (not heard of any SQLite
> DBs that big before).
> 
>> 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.
> 
> There is a body of evidence to suggest SQLite is faster than many
> multiuser rdbms' for general use but apparently CREATE INDEX is one
> thing it does fall behind in.

Apparently it creates an empty table with the new structure, copies the 
data from the old table, then deletes the old table.

Extremely inefficient.

But I like SQLite.


> PGLoader recommended by Dimitri sounds excellent and I will check this
> out myself too as I have not heard of it before.
> 

[toc] | [prev] | [next] | [standalone]

#766

On 11.05.2017 17:11, DFS wrote:
> On 5/3/2017 8:57 AM, Terry Shanks wrote:

>> There is a body of evidence to suggest SQLite is faster than many
>> multiuser rdbms' for general use but apparently CREATE INDEX is one
>> thing it does fall behind in.
>
> Apparently it creates an empty table with the new structure, copies the
> data from the old table, then deletes the old table.

I do not understand: what do you mean by "new structure"?  When creating 
an index there is no new structure.  Or are you somehow alluding to 
changing the primary key of a table?

https://sqlite.org/withoutrowid.html

> Extremely inefficient.

That would be the case.  I doubt though that SQLite does it for every 
index creation.

Cheers

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[toc] | [prev] | [next] | [standalone]

#762

On Wed, 19 Apr 2017 20:11:46 +0100, Terry Shanks wrote:

> This will be fine as long as the server(s) are stopped at the time of
> copy,
> and they are they running the same version.

No need to stop the servers. pg_start_backup will do the trick.



-- 
Socijalizam je opijum za narod.

[toc] | [prev] | [next] | [standalone]

#741

DFS <nospam@dfs.com> writes:
> Windows
> Postgres 9.6.2
>
> On install I set the /data directory to a data drive (D:\)
>
> How much trouble will it cause if I:
>
> 1) backup that entire /data directory by copying it to a backup place
> 2) reinstall Postgres (and set the /data directory to the same
>    location as before)
> 3) copy over the new /data directory with my backup?
>
> No pgdump - just copy and replace files.

Is there a reason why you want to follow such a cumbersome and fragile
procedure?

[toc] | [prev] | [next] | [standalone]

#745

On 4/23/2017 5:15 PM, Rainer Weikusat wrote:
> DFS <nospam@dfs.com> writes:
>> Windows
>> Postgres 9.6.2
>>
>> On install I set the /data directory to a data drive (D:\)
>>
>> How much trouble will it cause if I:
>>
>> 1) backup that entire /data directory by copying it to a backup place
>> 2) reinstall Postgres (and set the /data directory to the same
>>     location as before)
>> 3) copy over the new /data directory with my backup?
>>
>> No pgdump - just copy and replace files.
> 
> Is there a reason why you want to follow such a cumbersome and fragile
> procedure?


1) it seems very uncumbersome to just copy a folder to a backup drive

2) why is it 'fragile'?

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.databases.postgresql

csiph-web