pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpgdump and restore results in different sizes DB
10+ messages / 5 participants
[nested] [flat]
* pgdump and restore results in different sizes DB
@ 2017-09-04 11:35 Jean R. Franco <jfranco@maila.biz>
0 siblings, 2 replies; 10+ messages in thread
From: Jean R. Franco @ 2017-09-04 11:35 UTC (permalink / raw)
To: pgsql-admin
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,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-04 11:45 Michael Vitale <michaeldba@sqlexec.com>
parent: Jean R. Franco <jfranco@maila.biz>
1 sibling, 0 replies; 10+ messages in thread
From: Michael Vitale @ 2017-09-04 11:45 UTC (permalink / raw)
To: Jean R. Franco <jfranco@maila.biz>; pgsql-admin
<!DOCTYPE html>
<html><head>
<meta charset="UTF-8">
</head><body><p>nice benefit of logical dump and restore: bye bye bloat</p><blockquote type="cite">On September 4, 2017 at 7:35 AM "Jean R. Franco" <jfranco@maila.biz> wrote:<br><br><div style="font-family: arial, sans-serif; font-size: 13px;"><br>Hello Everyone,<br><br>I'm moving a postgresql server from one server to another, both running versions 9.4.10<br>It's a single large database and I'm using pgdump to export and restoring on the new server.<br><br>The thing is, on the old server the database size is 81GB, but when I restore on the new server, it decreases to 60GB<br><br> List of databases<br> Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description<br>------------+----------+----------+---------+-------+-------------------+-------+------------+-------------<br> ecidade | postgres | LATIN1 | C | C | | 60 GB | pg_default |<br><br>I'm watching the whole process of restoring it and have no errors.<br>What could I been doing wrong?<br><br>Thanks,<br><br></div></blockquote></body></html>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-04 12:36 Jean R. Franco <jfranco@maila.biz>
parent: Jean R. Franco <jfranco@maila.biz>
1 sibling, 4 replies; 10+ messages in thread
From: Jean R. Franco @ 2017-09-04 12:36 UTC (permalink / raw)
To: Michael Vitale <michaeldba@sqlexec.com>; pgsql-admin
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,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-04 12:51 Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
parent: Jean R. Franco <jfranco@maila.biz>
3 siblings, 0 replies; 10+ messages in thread
From: Michaeldba@sqlexec.com @ 2017-09-04 12:51 UTC (permalink / raw)
To: Jean R. Franco <jfranco@maila.biz>; +Cc: pgsql-admin
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,
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-04 16:02 Keith <keith@keithf4.com>
parent: Jean R. Franco <jfranco@maila.biz>
3 siblings, 0 replies; 10+ messages in thread
From: Keith @ 2017-09-04 16:02 UTC (permalink / raw)
To: Jean R. Franco <jfranco@maila.biz>; +Cc: Michael Vitale <michaeldba@sqlexec.com>; pgsql-admin
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
> <%22Michael%20Vitale%22%20%3Cmichaeldba@sqlexec.com%3E>> 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,
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-04 18:15 Jean R. Franco <jfranco@maila.biz>
parent: Jean R. Franco <jfranco@maila.biz>
3 siblings, 1 reply; 10+ messages in thread
From: Jean R. Franco @ 2017-09-04 18:15 UTC (permalink / raw)
To: pgsql-admin
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,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-04 18:16 Jean R. Franco <jfranco@maila.biz>
parent: Jean R. Franco <jfranco@maila.biz>
3 siblings, 0 replies; 10+ messages in thread
From: Jean R. Franco @ 2017-09-04 18:16 UTC (permalink / raw)
To: pgsql-admin
Thanks Keith,
I´m afraid that this is also going to be my problem!
Best regards,
4 de Setembro de 2017 13:02, "Keith" escreveu:
On Mon, 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,
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/ (https://www.keithf4.com/cleaning-up-postgresql-bloat/)
Keith
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,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-04 21:53 Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
parent: Jean R. Franco <jfranco@maila.biz>
0 siblings, 2 replies; 10+ messages in thread
From: Michaeldba@sqlexec.com @ 2017-09-04 21:53 UTC (permalink / raw)
To: Jean R. Franco <jfranco@maila.biz>; +Cc: pgsql-admin
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,
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-05 10:30 Elvis Flesborg <elfle@sdfe.dk>
parent: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
1 sibling, 0 replies; 10+ messages in thread
From: Elvis Flesborg @ 2017-09-05 10:30 UTC (permalink / raw)
To: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>; Jean R. Franco <jfranco@maila.biz>; +Cc: pgsql-admin
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<mailto: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<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 <jfranco@maila.biz<mailto: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<mailto:%22Michael%20Vitale%22%20%3cmichaeldba@sqlexec.com%3e>> 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<mailto: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,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgdump and restore results in different sizes DB
@ 2017-09-11 01:06 Jean R. Franco <jfranco@maila.biz>
parent: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
1 sibling, 0 replies; 10+ messages in thread
From: Jean R. Franco @ 2017-09-11 01:06 UTC (permalink / raw)
To: pgsql-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,
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2017-09-11 01:06 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-09-04 11:35 pgdump and restore results in different sizes DB Jean R. Franco <jfranco@maila.biz>
2017-09-04 11:45 ` Michael Vitale <michaeldba@sqlexec.com>
2017-09-04 12:36 ` Jean R. Franco <jfranco@maila.biz>
2017-09-04 12:51 ` Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2017-09-04 16:02 ` Keith <keith@keithf4.com>
2017-09-04 18:15 ` Jean R. Franco <jfranco@maila.biz>
2017-09-04 21:53 ` Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2017-09-05 10:30 ` Elvis Flesborg <elfle@sdfe.dk>
2017-09-11 01:06 ` Jean R. Franco <jfranco@maila.biz>
2017-09-04 18:16 ` Jean R. Franco <jfranco@maila.biz>
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