Обсуждение: [ADMIN] pgdump and restore results in different sizes DB

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,

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" <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 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,

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 <jfranco@maila.biz> 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 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 <jfranco@maila.biz> 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" <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 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,

Thanks Keith,

I´m afraid that this is also going to be my problem!

Best regards,

4 de Setembro de 2017 13:02, "Keith" <keith@keithf4.com> escreveu:

On Mon, 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 that big of a size? Over 21G?

Thanks,

Most certainly. I've cleaned up hundreds of gigs when a system had never done any serious bloat monitoring/cleanup before.

https://www.keithf4.com/cleaning-up-postgresql-bloat/

Keith

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

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 <jfranco@maila.biz> wrote:

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" <elfle@sdfe.dk> 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] On Behalf Of Michaeldba@sqlexec.com
Sent: Monday, September 4, 2017 11:53 PM
To: Jean R. Franco <jfranco@maila.biz>
Cc: 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 <jfranco@maila.biz> 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 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 <jfranco@maila.biz> 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" <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 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,