From: Jean R. Franco <jfranco@maila.biz>
To: pgsql-admin@postgresql.org
Subject: Re: pgdump and restore results in different sizes DB
Date: Mon, 11 Sep 2017 01:06:19 +0000
Message-ID: <146e6023e2cefb420ed71bd732c03274@maila.biz> (raw)
In-Reply-To: <BA481260A6B9904BBA436DB43FCC012F15576775@S000014.PROD.SITAD.DK>
References: <BA481260A6B9904BBA436DB43FCC012F15576775@S000014.PROD.SITAD.DK>
<55858EF9-AACE-4478-809F-BB84D96FFD02@sqlexec.com>
<1758120710.400302.1504525501413@email.1and1.com>
<3e75543bdc093c158f050b3dfc0afe8e@maila.biz>
<9f30edef02a5c0f25f4c72fb191f9512@maila.biz>
<92e4f5bf6ac966939a3ec887a7917d07@maila.biz>
<A6C6F462-71A9-4EE5-AD1C-D7553E7705FF@sqlexec.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hello,
I ran this query and only 03 rows have different numbers:
pg_operator_oid_index
pg_collation_oid_index
pg_rewrite_oid_index
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" escreveu:
I had luck with this query:
`select nspname, relname, reltuples from pg_class left join pg_namespace on pg_namespace.oid = pg_class.relnamespace;`
If you want, you can throw an `order by nspname, relname` 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) [mailto:pgsql-admin-owner@postgresql.org (mailto:mailto:pgsql-admin-owner@postgresql.org)] On Behalf Of Michaeldba@sqlexec.com (mailto:Michaeldba@sqlexec.com)
Sent: Monday, September 4, 2017 11:53 PM
To: Jean R. Franco
Cc: pgsql-admin@postgresql.org (mailto:pgsql-admin@postgresql.org)
Subject: Re: [ADMIN] pgdump and restore results in different sizes DB
Get approximate counts the shortcut way:
Do analyze on all your user tables. Then, do a join with pg_namespace, pg_table(s), and pg_class and spit out the scheme, table name, and number of rows(reltuples).
Not near a computer so forgot if it is pg_table or pg_table.
Sent from my iPad
On Sep 4, 2017, at 2:15 PM, Jean R. Franco wrote:
Thanks 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 but that will take me a long time (1596).
I checked the table sizes and they differ very much!
Thanks,
4 de Setembro de 2017 09:54, Michaeldba@sqlexec.com (mailto:Michaeldba@sqlexec.com) escreveu:
Do select counts from all your tables in old and new and see if they match.
Sent from my iPad
On Sep 4, 2017, at 8:36 AM, Jean R. Franco wrote:
Hi Michael,
Thanks for replying,
Do you think it would be that big of a size? Over 21G?
Thanks,
4 de Setembro de 2017 08:45, "Michael Vitale" escreveu:
nice benefit of logical dump and restore: bye bye bloat
On September 4, 2017 at 7:35 AM "Jean R. Franco" 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 pgdump to export and restoring on the new server.
The thing is, on the old server the database size is 81GB, but when I restore on the new server, it decreases to 60GB
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------------+----------+----------+---------+-------+-------------------+-------+------------+-------------
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,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jfranco@maila.biz
Subject: Re: pgdump and restore results in different sizes DB
In-Reply-To: <146e6023e2cefb420ed71bd732c03274@maila.biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox