Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VX3so-00085b-GF for pgsql-admin@arkaria.postgresql.org; Fri, 18 Oct 2013 06:50:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VX3sn-00039C-JH for pgsql-admin@arkaria.postgresql.org; Fri, 18 Oct 2013 06:50:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VX3sl-000391-9L for pgsql-admin@postgresql.org; Fri, 18 Oct 2013 06:49:59 +0000 Received: from mailout.ownit.se ([94.137.101.210] helo=spamgw1.ownit.se) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VX3sb-0008Q1-Q8 for pgsql-admin@postgresql.org; Fri, 18 Oct 2013 06:49:57 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ownit.se; s=2010.dkim; h=received:received:content-type:mime-version:subject:from:in-reply-to:date:cc: message-id:references:to:x-mailer; bh=rNNNOxj7SpN3rm7sl/VAQzsCyYHnnLY3H0/N8rTqYv4=; b=YqhFJK+S6Kg5EUmQIvtdszehWN1AhIeiiv2T3Fpq7v3qChle79VzbT3mfQAi/55avacqLbUa/Kewn t7Y4cncIdmjgwBzSEjxdSOo93vszXidOTX43ntFV3qrAuIZunZrGkbfub+hDwzlqCXR0YAvVuUUnf7 5Kpz4ZgoShN5llWU= Received: from mail.ownit.nu (unknown [109.104.0.111]) by mailout.ownit.se (Halon Mail Gateway) with ESMTPS; Fri, 18 Oct 2013 08:48:52 +0200 (CEST) Received: from [192.168.1.6] (89-253-107-135.customers.ownit.se [89.253.107.135]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by mail.ownit.nu (Postfix) with ESMTPS id D2F25FE7D0; Fri, 18 Oct 2013 08:48:52 +0200 (CEST) Content-Type: multipart/alternative; boundary="Apple-Mail=_DDF6BBCE-847B-442A-969D-F97204EC537E" Mime-Version: 1.0 (Mac OS X Mail 6.5 \(1508\)) Subject: Re: Strange results using pg_restore From: reiner peterke In-Reply-To: Date: Fri, 18 Oct 2013 08:48:22 +0200 Cc: pgsql-admin@postgresql.org Message-Id: References: To: Oscar Calderon X-Mailer: Apple Mail (2.1508) X-Pg-Spam-Score: -1.1 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --Apple-Mail=_DDF6BBCE-847B-442A-969D-F97204EC537E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=iso-8859-1 Hi Oscar, I've done some migration work on several of our databases, in the = 150-200 GB range. The times have been around 20-40 minuets. So, depending on the hardware your times seem to be reasonable. Regarding the smaller size, doing a pg_restore the tables and indexes = will be of 'optimal' size. the restore will build the tables and = indexes without any wasted space so there should be a reduction in size = over the databases. check the number of rows and look at sample data. when i'm wanting to = do a quick comparison i'll use a query like, select to_char('','yyyy-mm') dt, count(*) from = group by dt; and compare the results between the two. If you have the disk space i would recommend using the pg_upgrade = command. http://www.postgresql.org/docs/9.3/interactive/pgupgrade.html reiner On 17 okt 2013, at 17:48, Oscar Calderon = wrote: > Have a good day. This friday i'm going to migrate an entire database = of a government institution in my country. Those are like 4 database of = 2GB each one. So, i was preparing about what i'm going to do tomorrow. = They currently have PostgreSQL 9.1 installed from source i think, and = they will update RHEL version to 5.7, it will be a reinstall of = operative system, so before that i will backup the databases, copy = contents of pg_hba.conf and so on, and after the reinstallation of RHEL = i'm going to install PostgreSQL 9.3 from official repositories alongside = contrib and plpython.=20 >=20 > I've already tested that on a Centos 5.7 VM and it works fine. But = well, yesterday i was practicing creation of backups and restoring = backups in my laptop with the next specs: >=20 > Core i7 2.2 Ghz > 8GB RAM > 30GB empty space > Windows 8.1 >=20 > In my laptop i also have PostgreSQL 9.3. First i used the sample = database pgdellstore, what i did is that i created a database, and ran = the SQL file of the database. After that, i created a backup of that = database pgdellstore with pg_dump, a custom format, without compression: >=20 > pg_dump -h localhost -p 5432 -U postgres -W -Fc -Z0 -C -d pgdellstore = > pgdellstore.backup >=20 > And everything as normal. After that i created a second database and = restored my custom backup with pg_restore trying to speed up using -j = option: >=20 > pg_restore -h localhost -p 5432 -U postgres -W -d pgdellstore -j4 = pgdellstore.backup >=20 > Also, before that, i backed up my postgresql.conf and edited the = current one with the next parameters, according to some research that i = did in google about performance tuning of pg_restore: >=20 > shared_buffers=3D2GB > maintenance_work_mem=3D1GB > work_mem=3D128MB > wal_buffers=3D16MB=20 > checkpoint_segments=3D8=20 > autovacuum=3Doff > archive_mode=3Doff=20 > fsync=3Doff=20 > full_page_writes=3Doff > checkpoint_timeout=3D15min=20 > checkpoint_completion_target=3D0.9 > track_counts=3Doff > synchronous_commit=3Doff=20 > bgwriter_delay=3D50ms >=20 > And it restored it in 2 seconds i think, for me it was extremely fast = (is a database of 25MB aproximately) and i had doubts, so i have some = queries to check database tables size and i compared the results of the = first database (the one that i created with original sql script) and the = second one (the one that i created using a custom backup of the first = one) and i got this: >=20 > DB created with original SQL script: >=20 > schemaname | tablename | reltuples | tamanio | tamanioord > ------------+--------------+-----------+------------+------------ > public | customers | 20000 | 5016 kB | 5136384 > public | orderlines | 60350 | 4440 kB | 4546560 > public | cust_hist | 60350 | 3976 kB | 4071424 > public | products | 10000 | 1552 kB | 1589248 > public | orders | 12000 | 1384 kB | 1417216 > public | inventory | 10000 | 704 kB | 720896 > public | categories | 16 | 24 kB | 24576 > public | afiliado | 4 | 24 kB | 24576 > public | pruebafechas | 0 | 8192 bytes | 8192 > public | reorder | 0 | 0 bytes | 0 > (10 filas) >=20 > DB created with custom backup based on first db >=20 > schemaname | tablename | reltuples | tamanio | tamanioord > ------------+--------------+-----------+------------+------------ > public | customers | 20000 | 4992 kB | 5111808 > public | orderlines | 60350 | 4416 kB | 4521984 > public | cust_hist | 60350 | 3952 kB | 4046848 > public | products | 10000 | 1528 kB | 1564672 > public | orders | 12000 | 1360 kB | 1392640 > public | inventory | 10000 | 680 kB | 696320 > public | afiliado | 4 | 24 kB | 24576 > public | categories | 16 | 24 kB | 24576 > public | pruebafechas | 0 | 8192 bytes | 8192 > public | reorder | 0 | 0 bytes | 0 > (10 filas) >=20 > This is the query that i used to get those results: >=20 > SELECT tbl.schemaname, tbl.tablename, obj.reltuples > , pg_size_pretty(pg_total_relation_size(text('"' || tbl.schemaname || = '"."' || tbl.tablename || '"'))) tamanio, = pg_total_relation_size(text('"' || tbl.schemaname || '"."' || = tbl.tablename || '"')) tamanioord > FROM pg_tables tbl, pg_class obj WHERE tbl.tablename =3D obj.relname > AND tbl.schemaname NOT IN ('pg_catalog', 'information_schema') ORDER = BY tamanioord DESC; >=20 > As you can see, there are certain variations in size, the number of = tuples remains, but the fields tamanio (formatted size) and tamanioord = (size without format) have a difference, for example the table customers = has 5016 kB versus 4992 kB . I'm afraid that in someway i lost data or = something like that, or according to your experience that can be normal = and doesn't mean that information is corrupted or something, because = tomorrow when i will do that with productions databases it will be a = chaos if information gets corrupted. >=20 > Also, my second concern is the next: 4 months ago, i did a routine = maintenance of those production database, and first as usual i backup = all the databases. I took one of the backups of a development database = for testing purposes. The backup file weights 279MB and the database is = like 1.8 GB . So, yesterday i also practiced restoring this backup with = pg_restore in a new database, with the same configuration from above. = The command used was this: >=20 > pg_restore -h localhost -p 5432 -U postgres -W --create -d debdbrest = -j4 devdb.backup >=20 > And it took only 1:30 mins aproximately. For me is suspiciously fast = becuse when i was reading about pg_restore and how to enhace performance = during backup restoring, i found various cases in previous postgresql = versions about pg_restore is too slow, and also, i tried the same = restore of the same backups 2 days ago but without postgresql.conf = parameters modified and without using -j flag, and it started and after = 2 hours it hadn't finished, so i cancelled; also in the first try i = noted that it printed a lot of output in the console and in this second = try it didn't print anything. >=20 > But that time reduction from more than 2 hours to 1:30 mins is too = suspicious for me, but i don't know if, as the previous question that i = have, you consider that this is normal (a backup of 279MB of a database = of 1840MB restored in just 1.30 min). >=20 > Regards. >=20 > *************************** > Oscar Calderon > Analista de Sistemas > Soluciones Aplicativas S.A. de C.V. > www.solucionesaplicativas.com > Cel. (503) 7741 7850 Tel. (503) 2522-2834 --Apple-Mail=_DDF6BBCE-847B-442A-969D-F97204EC537E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=iso-8859-1 Hi = Oscar,

I've done some migration work on several of = our databases, in the 150-200 GB  range.  The times have been = around 20-40 minuets.
So, depending on the hardware your times = seem to be reasonable.

Regarding the smaller = size, doing a pg_restore the tables and indexes will be of 'optimal' = size.  the restore will build the tables and indexes without any = wasted space so there should be a reduction in size over the = databases.

check the number of rows and look at = sample data.  when i'm wanting to do a quick comparison i'll use a = query like,
select = to_char('<timespamp_column>','yyyy-mm') dt, count(*) from = <table> group by dt;
and compare the results between the = two.

If you have the disk space i would = recommend using the pg_upgrade command.
<= br>
reiner

On 17 okt 2013, = at 17:48, Oscar Calderon <ocalderon@solucionesap= licativas.com> wrote:

Have a good day. This friday i'm going to migrate an entire = database of a government institution in my country. Those are like 4 = database of 2GB each one. So, i was preparing about what i'm going to do = tomorrow. They currently have PostgreSQL 9.1 installed from source i = think, and they will update RHEL version to 5.7, it will be a reinstall = of operative system, so before that i will backup the databases, copy = contents of pg_hba.conf and so on, and after the reinstallation of RHEL = i'm going to install PostgreSQL 9.3 from official repositories alongside = contrib and plpython. 

I've already tested that on a Centos 5.7 VM and it works = fine. But well, yesterday i was practicing creation of backups and = restoring backups in my laptop with the next = specs:

Core i7 2.2 Ghz
8GB RAM
30GB empty space
Windows = 8.1

In my laptop i also have PostgreSQL 9.3. = First i used the sample database pgdellstore, what i did is that i = created a database, and ran the SQL file of the database. After that, i = created a backup of that database pgdellstore with pg_dump, a custom = format, without compression:

pg_dump -h localhost -p 5432 -U postgres -W -Fc -Z0 = -C -d pgdellstore > = pgdellstore.backup

And everything as = normal. After that i created a second database and restored my custom = backup with pg_restore trying to speed up using -j option:

pg_restore -h localhost -p 5432 -U postgres -W -d = pgdellstore -j4 pgdellstore.backup

Also, = before that, i backed up my postgresql.conf and edited the current one = with the next parameters, according to some research that i did in = google about performance tuning of pg_restore:
=

shared_buffers=3D2GB
maintenance_work_m= em=3D1GB
work_mem=3D128MB
wal_buffers=3D16MB 
checkpoint_segments=3D8 
autovacuum=3Doff
= archive_mode=3Doff 
=
fsync=3Doff 
full_page_writes=3Doff
checkpoi= nt_timeout=3D15min 
checkpoint_completion_target=3D0.9
track_counts=3Doff
synchronous_commit=3Doff 
bgwriter_delay=3D50ms

And it restored it in 2 seconds i think, for = me it was extremely fast (is a database of 25MB aproximately) and i had = doubts, so i have some queries to check database tables size and i = compared the results of the first database (the one that i created with = original sql script) and the second one (the one that i created using a = custom backup of the first one) and i got this:

DB created with original SQL = script:

 schemaname |  tablename =   | reltuples |  tamanio   | = tamanioord
------------+--------------+-----------+----= --------+------------
 public     | customers    |     = 20000 | 5016 kB    |   =  5136384
 public     | orderlines   | =     60350 | 4440 kB    |   =  4546560
 public     | cust_hist   =  |     60350 | 3976 kB    |   =  4071424
 public     | products     |     = 10000 | 1552 kB    |   =  1589248
 public     | orders   =     |     12000 | 1384 kB    |   =  1417216
 public     | inventory   =  |     10000 | 704 kB     |     = 720896
 public     | categories   |     =    16 | 24 kB      |     =  24576
 public     | afiliado   =   |         4 | 24 kB      | =      24576
 public     | = pruebafechas |         0 | 8192 bytes |   =     8192
 public     | reorder      |   =       0 | 0 bytes    |       =    0
(10 filas)

DB = created with custom backup based on first = db

 schemaname |  tablename =   | reltuples |  tamanio   | tamanioord
=
------------+--------------+-----------+------------+------------
 public     | customers    |   =   20000 | 4992 kB    |   =  5111808
 public     | orderlines   | =     60350 | 4416 kB    |    4521984
 public     | cust_hist    |     = 60350 | 3952 kB    |   =  4046848
 public     | products   =   |     10000 | 1528 kB    |   =  1564672
 public     | orders   =     |     12000 | 1360 kB    |   =  1392640
 public     | inventory    |     = 10000 | 680 kB     |     = 696320
 public     | afiliado     | =         4 | 24 kB      |   =    24576
 public     | categories =   |        16 | 24 kB      | =      24576
 public     | pruebafechas |       =   0 | 8192 bytes |       8192
 public =     | reorder      |       =   0 | 0 bytes    |         =  0
(10 filas)

This is the = query that i used to get those results:

SELECT tbl.schemaname, tbl.tablename, = obj.reltuples
, pg_size_pretty(pg_total_relation_size(text('"' = || tbl.schemaname || '"."' || tbl.tablename || '"'))) tamanio, = pg_total_relation_size(text('"' || tbl.schemaname || '"."' || = tbl.tablename || '"')) tamanioord
FROM pg_tables tbl, pg_class obj WHERE tbl.tablename =3D = obj.relname
AND tbl.schemaname NOT IN ('pg_catalog', = 'information_schema') ORDER BY tamanioord = DESC;

As you can see, there are certain = variations in size, the number of tuples remains, but the fields tamanio = (formatted size) and tamanioord (size without format) have a difference, = for example the table customers has 5016 kB versus 4992 kB . I'm afraid = that in someway i lost data or something like that, or according to your = experience that can be normal and doesn't mean that information is = corrupted or something, because tomorrow when i will do that with = productions databases it will be a chaos if information gets = corrupted.

Also, my second concern is the next: 4 months ago, i = did a routine maintenance of those production database, and first as = usual i backup all the databases. I took one of the backups of a = development database for testing purposes. The backup file weights 279MB = and the database is like 1.8 GB  . So, yesterday i also practiced = restoring this backup with pg_restore in a new database, with the same = configuration from above. The command used was this:

pg_restore -h localhost -p 5432 -U postgres -W = --create -d debdbrest -j4 devdb.backup

And = it took only 1:30 mins aproximately. For me is suspiciously fast becuse = when i was reading about pg_restore and how to enhace performance during = backup restoring, i found various cases in previous postgresql versions = about pg_restore is too slow, and also, i tried the same restore of the = same backups 2 days ago but without postgresql.conf parameters modified = and without using -j flag, and it started and after 2 hours it hadn't = finished, so i cancelled; also in the first try i noted that it printed = a lot of output in the console and in this second try it didn't print = anything.

But that time reduction from more than 2 hours to = 1:30 mins is too suspicious for me, but i don't know if, as the previous = question that i have, you consider that this is normal (a backup of = 279MB of a database of 1840MB restored in just 1.30 min).

Regards.

***************************
Oscar Calderon
Analista de = Sistemas
Soluciones Aplicativas S.A. de C.V.
www.solucionesaplicativas.com
Cel. (503) 7741 7850 Tel. (503) 2522-2834

= --Apple-Mail=_DDF6BBCE-847B-442A-969D-F97204EC537E--