Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1drDDG-0008R1-E2 for pgsql-admin@arkaria.postgresql.org; Mon, 11 Sep 2017 01:08:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1drDDF-0001mj-IV for pgsql-admin@arkaria.postgresql.org; Mon, 11 Sep 2017 01:08:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1drDBQ-00072R-2d for pgsql-admin@postgresql.org; Mon, 11 Sep 2017 01:06:40 +0000 Received: from my.maila.biz ([138.118.35.245]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1drDBF-0002Le-SK for pgsql-admin@postgresql.org; Mon, 11 Sep 2017 01:06:38 +0000 Received: (Haraka outbound); Mon, 11 Sep 2017 01:06:23 +0000 Authentication-Results: my.maila.biz; auth=pass (login) Received: from webmail.maila.biz (e9fe2f8a-27e0-4f92-a13d-5b2f3de0e680.cloudron [172.18.0.13]) by my.maila.biz (Haraka/2.8.13) with ESMTPA id F998F0B3-7697-4B08-995F-534E8DA289C8.1 envelope-from (authenticated bits=0); Mon, 11 Sep 2017 01:06:20 +0000 Mime-Version: 1.0 Date: Mon, 11 Sep 2017 01:06:19 +0000 Content-Type: multipart/alternative; boundary="--=_RainLoop_563_502481828.1505091979" X-Mailer: RainLoop/1.11.1 From: "Jean R. Franco" Message-ID: <146e6023e2cefb420ed71bd732c03274@maila.biz> Subject: Re: pgdump and restore results in different sizes DB To: pgsql-admin@postgresql.org In-Reply-To: References: <55858EF9-AACE-4478-809F-BB84D96FFD02@sqlexec.com> <1758120710.400302.1504525501413@email.1and1.com> <3e75543bdc093c158f050b3dfc0afe8e@maila.biz> <9f30edef02a5c0f25f4c72fb191f9512@maila.biz> <92e4f5bf6ac966939a3ec887a7917d07@maila.biz> X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on 8cf618c304ea X-Spam-Level: X-Spam-Status: No, score=-2.9 required=5.0 tests=ALL_TRUSTED,BAYES_00, HTML_MESSAGE autolearn=ham autolearn_force=no version=3.4.1 DKIM-Signature: v=1;a=rsa-sha256;bh=H7Q3niIf+r/vYGAhBmE297B6xknD/5qJZGo0wX1RT8s=;c=relaxed/simple;d=maila.biz;h=from:subject:date:message-id:to:mime-version;s=cloudron;b=X/RWn5sTzjVheCZuw5oYoZWV26atNwAVk9RQWN8+H/g9F90YRMQDcK5u0DPQGviR9YJQDlqL/p/gunjte0cMzK2NuEiUw11HKSyyi3Esv5MvsMt3U+xF4goOvskpgDihBusbcq+pvStqD79a/dBU5PGur9zVdPY9+87/vKOeuiI= 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 ----=_RainLoop_563_502481828.1505091979 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hello,=0A=0AI ran this query and only 03 rows have different numbers:=0Ap= g_operator_oid_index=0Apg_collation_oid_index=0Apg_rewrite_oid_index=0A= =0ASome lower and some higher numbers.=0A=0AAll the others match.=0A=0A/l= + databasename reveals a difference of almost 20GB still.=0A=0AI tested t= he application and everything seems fine.=0A=0AShould I worry?=0A=0AThank= s,=0A=0A5 de Setembro de 2017 07:30, "Elvis Flesborg" escreveu:=0A I had= luck with this query: =0A=0A `select nspname, relname, reltuples from pg= _class left join pg_namespace on pg_namespace.oid =3D pg_class.relnamespa= ce;` =0A=0A If you want, you can throw an `order by nspname, relname` in = there, then it will be easier to compare the two clusters. =0A=0A -Elvis = =0A From: pgsql-admin-owner@postgresql.org (mailto:pgsql-admin-owner@post= gresql.org) [mailto:pgsql-admin-owner@postgresql.org (mailto:mailto:pgsql= -admin-owner@postgresql.org)] On Behalf Of Michaeldba@sqlexec.com (mailto= :Michaeldba@sqlexec.com)=0ASent: Monday, September 4, 2017 11:53 PM=0ATo:= Jean R. Franco =0ACc: pgsql-admin@postgresql.org (mailto:pgsql-admin@pos= tgresql.org)=0ASubject: Re: [ADMIN] pgdump and restore results in differe= nt sizes DB =0A Get approximate counts the shortcut way: =0A Do analyze o= n all your user tables. Then, do a join with pg_namespace, pg_table(s), a= nd pg_class and spit out the scheme, table name, and number of rows(reltu= ples). =0A Not near a computer so forgot if it is pg_table or pg_table.= =0A=0ASent from my iPad =0AOn Sep 4, 2017, at 2:15 PM, Jean R. Franco wr= ote: =0A Thanks for replying,=0A=0AThat takes me to another question:=0A-= Is there a way to do a count on all the tables at once?=0AI can check on= e by one but that will take me a long time (1596).=0A=0AI checked the tab= le sizes and they differ very much!=0A=0AThanks,=0A=0A4 de Setembro de 20= 17 09:54, Michaeldba@sqlexec.com (mailto:Michaeldba@sqlexec.com) escreve= u: =0A Do select counts from all your tables in old and new and see if th= ey match.=0A=0ASent from my iPad =0AOn Sep 4, 2017, at 8:36 AM, Jean R. F= ranco wrote: =0A Hi Michael,=0A=0AThanks for replying,=0A=0ADo you think= it would be that big of a size? Over 21G?=0A=0AThanks,=0A=0A4 de Setembr= o de 2017 08:45, "Michael Vitale" escreveu: =0A nice benefit of logical = dump and restore: bye bye bloat =0A=0A On September 4, 2017 at 7:35 AM "= Jean R. Franco" wrote: =0AHello Everyone,=0A=0AI'm moving a postgresql s= erver from one server to another, both running versions 9.4.10=0AIt's a s= ingle large database and I'm using pgdump to export and restoring on the = new server.=0A=0AThe thing is, on the old server the database size is 81G= B, but when I restore on the new server, it decreases to 60GB=0A=0AList o= f databases=0AName | Owner | Encoding | Collate | Ctype | Access privileg= es | Size | Tablespace | Description=0A------------+----------+----------= +---------+-------+-------------------+-------+------------+-------------= =0Aecidade | postgres | LATIN1 | C | C | | 60 GB | pg_default |=0A=0AI'm = watching the whole process of restoring it and have no errors.=0AWhat cou= ld I been doing wrong?=0A=0AThanks, ----=_RainLoop_563_502481828.1505091979 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable
H= ello,

I ran this query and only 03 rows have different numbers:pg_operator_oid_index
pg_collation_oid_index
pg_rewrite_oid_index<= br>
Some lower and some higher numbers.

All the others match.
/l+ databasename reveals a difference of almost 20GB still.

= I tested the application and everything seems fine.

Should I worry= ?

Thanks,

5 de Setembro de 2017 07:30, "Elvis Flesborg" <= ;elfle@sdfe.dk> escreveu:

I had luck with this query:

= `select nspname, rel= name, reltuples from pg_class left join pg_namespace on pg_namespace.oid = =3D pg_class.relnamespace;`

If you want, you can throw an `order by nspname, re= lname` in there, then it will be easier to compare the two clusters.

-Elvis

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Beh= alf Of Michaeldba@sqlexec.com
Sent: Mond= ay, September 4, 2017 11:53 PM
To: Jean R. Franco <jfranco@maila.biz>
Cc: pgsql-admin@p= ostgresql.org
Subject: Re: [ADMIN] pgdump and restore resul= ts in different sizes DB

Get approximate c= ounts the shortcut way:

Do analyze on all your user tab= les. Then, do a join with pg_namespace, pg_table(s), and pg_class and spi= t out the scheme, table name, and number of rows(reltuples).

Not near a computer so forgot if it is pg_table or pg_t= able.

Sent from my iPad


On Sep 4, 2017, at 2:15 PM, Jean R. Franco <jfranco@maila.biz> wrote:

=
<= p>Thank= s for replying,

That takes me to another question:
- Is there a= way to do a count on all the tables at once?
I can check one by one b= ut that will take me a long time (1596).

I checked the table sizes= and they differ very much!

Thanks,

4 de Setembro de 2017 0= 9:54, Michaeldba@sqlexe= c.com escreveu:

Do select counts from all your tables in o= ld and new and see if they match.

Sent from my iPad


On Sep 4, 2017, at 8:36 AM, Jean R. Franco <jfranco@maila.biz> wrote:

Hi= Michael,

Thanks for replying,

Do you think it would be tha= t big of a size? Over 21G?

Thanks,

4 de Setembro de 2017 08= :45, "Michael Vitale" <michaeldba@sqlexec.com> escreveu:=

=

nice benefit of logical dump and restore: bye bye bloat

=

On September 4= , 2017 at 7:35 AM "Jean R. Franco" <jfranco@maila.biz> wrote:


Hello Everyone= ,

I'm moving a postgresql server from one server to another, both = running versions 9.4.10
It's a single large database and I'm using pgd= ump to export and restoring on the new server.

The thing is, on th= e old server the database size is 81GB, but when I restore on the new ser= ver, it decreases to 60GB

List of databases
Name | Owner | Enco= ding | Collate | Ctype | Access privileges | Size | Tablespace | Descript= ion
------------+----------+----------+---------+-------+-------------= ------+-------+------------+-------------
ecidade | postgres | LATIN1 = | C | C | | 60 GB | pg_default |

I'm watching the whole process of= restoring it and have no errors.
What could I been doing wrong?
Thanks,

=
<= /div>

<= /html> ----=_RainLoop_563_502481828.1505091979--