Re: [GENERAL] database size changed after restoring using pg_restore

[John R Pierce]

On 11/8/2017 2:59 PM, Dylan Luong wrote:


Hi

I am upgrading some databases from a PostgreSQL 9.2 server to a new 
PostgreSQL 9.6 server.


I used pg_dump and pg_restore for the upgrade.

.

But when I listed the size of the database (postgres=# \l+) between 
the 9.2 and the upgraded 9.6, they were different.


on 9.2 it was 3776 MB
on 9.6 it was 1570 MB

I also did a few more databases using the same steps and they all 
appeared to be smaller. Is that normal?



yes.  a freshly restored database will be all contiguous with no 
embedded free tuples left over from operations.   databases that get 
updates (or inserts/deletes) tend to bloat.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] database size changed after restoring using pg_restore

[Andreas Joseph Krogh]

På onsdag 08. november 2017 kl. 23:59:40, skrev Dylan Luong <
dylan.lu...@unisa.edu.au >:
Hi 

I am upgrading some databases from a PostgreSQL 9.2 server to a new 
PostgreSQL 9.6 server.

 

I used pg_dump and pg_restore for the upgrade.

 

This is the command I used to dump the database. I ran the command from the 
9.6 server.

pg_dump -h 9.2server -j 16 --format=directory -f 
/pg_backup/backup/dump/mydb-20171108.dmp -U postgres -W mydb

 

This is the command I used to restore the database on the 9.6server.

pg_restore -j 16 -C -d postgres /pg_backup/backup/dump/mydb-20171108.dmp

 

Everything appears ok, the dump and restore completed without and errors.

 

But when I listed the size of the database (postgres=# \l+) between the 9.2 
and the upgraded 9.6, they were different.

 

on 9.2 it was 3776 MB

on 9.6 it was 1570 MB

 

I also did a few more databases using the same steps and they all appeared to 
be smaller. Is that normal?


Yep.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Database size on disk

[dinesh kumar]

On Mon, Oct 5, 2015 at 12:47 PM, Quiroga, Damian 
wrote:

> Hi,
>
>
>
> I’m looking for some advice on how to determine the DB size on disk to
> trigger some cleanup tasks (deleting old data) if some threshold is reached.
>
>
>
> Let’s say that for simplicity I don’t want to ask for the size on disk to
> the OS, but rather to PostgreSQL itself. And I only have a single database
> other than the defaults (‘template’, etc).
>
>
>
> In that scenario is using pg_database_size() reasonable? Does that
> function return the actual size on disk or is it a calculation? Is there a
> better alternative?
>
>
>

Yes, pg_database_size() gives the size from disk level. IIRC, it do "du -h
" kind of method.




> Thanks,
>
>
>
> Damian
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [GENERAL] Database size

[John R Pierce]

Leonardo M. Ramé wrote:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.
  



the space occupied by deleted tuples will available for reuse after they 
are vacuumed...  so it might not shrink the database but it will help 
keep the database from growing.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database size

[Francisco Reyes]

Leonardo M. Ramé writes:


A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?.


No.


For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.


Not sure about ByteA, but in general... in potgres you need to do that or a 
vacuum full.. or copy all the data of the table into a new table and rename.


Having said that.. if you delete old rows and do a vacuum and/or have 
autovacuum enabled the  space of those old rows can be re-used, slowing down 
the growth of the table. So yes, deleting old rows and doing a vacuum is a 
good thing.. but no I don't believe it will reduce database size.


I say don't believe because I don't recall if byteA was stored in the 
table itself or was stored outside using TOAST.. so I am not sure about 
how/when space is released for it.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database size

[John R Pierce]

Francisco Reyes wrote:
I say don't believe because I don't recall if byteA was stored in 
the table itself or was stored outside using TOAST.. so I am not sure 
about how/when space is released for it.


like all other data, that depends on the size of the data.if the 
entire row (tuple) is under 4K (I think, don't quote me), it will be 
stored in the table, otherwise it will be toasted and stored in the 
PG_TOAST tables which also need vacuuming




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database size

[Raymond O'Donnell]

On 11/01/2010 18:00, Leonardo M. Ramé wrote:
 A customer of mine asked me to periodically delete old, unneeded records
 containing ByteA fields, because he think it will reduce the database
 size on disk. Is this true?. For example, in Firebird, the database size
 is not reduced until you do a Backup-Restore of the database.

When you delete a row, the space isn't reclaimed until you do a VACUUM.

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database size

[Steve Crawford]

Leonardo M. Ramé wrote:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.
  
From this perspective, bytea should be pretty much like any other data 
in PostgreSQL.


If you delete the records, the on-disk size will not be reduced.

If you vacuum the table after deleting (or let autovacuum do it for 
you), the on-disk size will not be reduced but the space formerly 
occupied by the deleted records can be reused to store new data.


To actually shrink the on-disk size requires a vacuum full or a cluster. 
Cluster is typically preferred since it is far faster and rebuilds the 
indexes but it does require sufficient disk-space to hold the original 
copy and the new copy of the data.


Both vacuum full and cluster lock the table.

If you delete unneeded records promptly and in small batches (and 
assuming no weird distribution of bytea data-sizes), autovaccuum should 
to a reasonable job of keeping bloat under control.


Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database size

[Craig Ringer]

On 12/01/2010 2:00 AM, Leonardo M. Ramé wrote:

A customer of mine asked me to periodically delete old, unneeded records
containing ByteA fields, because he think it will reduce the database
size on disk. Is this true?. For example, in Firebird, the database size
is not reduced until you do a Backup-Restore of the database.


It won't shrink it unless you do a VACUUM FULL or CLUSTER, but it *will* 
prevent growth or (if you're adding more data than you're deleting) 
reduce the rate of growth.


Make sure autovacuum is running and (for pre-8.4 databases) your free 
space map settings are sufficient.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] database size grows (even after vacuum (full and analyze))....

[João Miguel Ferreira]

Title: RE: [GENERAL] database size grows (even after vacuum (full and analyze)) 






Ok.

I get the point. I'm using 7.2 because that's the one I got from the original Fedora Core 3 CD's.

I'll upgrade to the most recent.

Thank you all for your support.

jmf



-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Mon 5/8/2006 7:08 PM
To: Bruno Wolff III
Cc: João Miguel Ferreira; pgsql-general@postgresql.org
Subject: Re: [GENERAL] database size grows (even after vacuum (full and analyze))

Bruno Wolff III [EMAIL PROTECTED] writes:
 In the long run, you should upgrade. 7.2 is essentially without support. I
 beleive there is still a RHEL version using it that is in support, so a
 critical fix might get back ported.

No, Red Hat never shipped a RHEL version using 7.2.* (they went straight
from 7.1 to 7.3). This is not unrelated to the fact that the community
dropped support for 7.2, actually --- I'm sure we'd not be maintaining
7.3 anymore either, if I weren't personally on the hook to support 7.3
for RHEL3.

Bottom line is there's no one out there maintaining 7.2 at all, and even
7.3 and 7.4 are really not getting anything but the most critical bug fixes.

   regards, tom lane

Re: [GENERAL] database size grows (even after vacuum (full and analyze))....

[Bruno Wolff III]

Please keep replies copied to the list so that others can learn from and
contribute to the discussion. I don't remember where this was, but it looks
like general is probably reasonable.

On Mon, May 08, 2006 at 15:02:20 +0100,
  Joao Miguel Ferreira [EMAIL PROTECTED] wrote:
 Bruno and all,
 
  There are a couple of possibilities worth checking. One is that there aren't
  idle transactions staying open for a long time. These would prevent vacuum
  from removing deleted rows as these transactions could still see them.
 
 I've checked that. No open transactions.
 
  Another possibility is that the FSM is too low and there isn't enough space 
  to
  track all of rows that can be recovered. (Vacuuming more often will also
  reduce the needed FSM setting.)
 Didn't check this!
 
  A third possible issue is index bloat, which
  can happen on older versions (7.4ish) of Postgres where key values increase 
  (or
  decrease) montonicly.
 
 REINDEX(ing) the indexes causes a sudden drop in filesystem usage, but
 after a while it size gets back to the value when it dropped and still
 grwoing (sime 100 to 300 Bytes/minute).
 
 Yes, my tables contains ever grwoing values afected by a UNIQUE
 constraint.
 
 What would I do next ?
 
 jmf
 (Pg is 7.2, rpm install, Fedora Core 3)

7.2 is subject to index bloat for indexes where the column increase monotonicly
and old values are deleted. In the short run you will want to schedule
regular reindexes.

In the long run, you should upgrade. 7.2 is essentially without support. I
beleive there is still a RHEL version using it that is in support, so a
critical fix might get back ported.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] database size grows (even after vacuum (full and analyze))....

[Joao Miguel Ferreira]

On Mon, 2006-05-08 at 18:07, Bruno Wolff III wrote:
 Please keep replies copied to the list so that others can learn from and
 contribute to the discussion. I don't remember where this was, but it looks
 like general is probably reasonable.

Sorry. I didn't notice. I'll keep that in mind.

  
  Yes, my tables contains ever grwoing values afected by a UNIQUE
  constraint.
  
  What would I do next ?
  
  jmf
  (Pg is 7.2, rpm install, Fedora Core 3)
 
 7.2 is subject to index bloat for indexes where the column increase 
 monotonicly
 and old values are deleted. In the short run you will want to schedule
 regular reindexes.

Well... that seems to answer my questions. Thanks.

 
 In the long run, you should upgrade. 7.2 is essentially without support. I
 beleive there is still a RHEL version using it that is in support, so a
 critical fix might get back ported.

I'll do that.

thanks

jmf




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] database size grows (even after vacuum (full and analyze))....

[Tom Lane]

Bruno Wolff III [EMAIL PROTECTED] writes:
 In the long run, you should upgrade. 7.2 is essentially without support. I
 beleive there is still a RHEL version using it that is in support, so a
 critical fix might get back ported.

No, Red Hat never shipped a RHEL version using 7.2.* (they went straight
from 7.1 to 7.3).  This is not unrelated to the fact that the community
dropped support for 7.2, actually --- I'm sure we'd not be maintaining
7.3 anymore either, if I weren't personally on the hook to support 7.3
for RHEL3.

Bottom line is there's no one out there maintaining 7.2 at all, and even
7.3 and 7.4 are really not getting anything but the most critical bug fixes.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Database Size Limiting

[Ben-Nes Michael]

Title: Database Size Limiting



I wonder if this will work:

each DB should assigned a diffrent user with set of 
premissions ( including insert ).

a script that can revoke the inseret privilage will 
emarge from time to time to check the DB size.

--Canaan Surfing Ltd.Internet Service 
ProvidersBen-Nes Michael - ManagerTel: 972-4-6991122Fax: 
972-4-6990098http://www.canaan.net.il--

  - Original Message - 
  From: 
  Campano, Troy 
  To: Postgres general mailing list 
  
  Sent: Thursday, May 27, 2004 8:07 
PM
  Subject: [GENERAL] Database Size 
  Limiting
  
  
  Is 
  there a way to limit the size that an individual database can take 
  up?
  My understanding is 
  that all your databases go into a file system which is your 
  ‘catalog 
  cluster’.
  But all the 
  databases can grow until that filesystem is full.
  Is there a way to 
  limit how big a database can be? Can you allocated space when the database is 
  set up so you can say db1 can only be 100 MB?
  
  Thank 
  you!
  Troy 
  Campano

Re: [GENERAL] Database Size Limiting

[Richard Huxton]

Campano, Troy wrote:
Is it possible to have a database spread across two data files?
So if it fills up filesystem1 then we could add a second filesystem and
allow it to grow onto this second filesystem?
If you're using Linux there is a tool called the Linux Volume Manager 
(LVM) which does this. You can change volume sizes dynamically, add new 
disks etc.

I think all modern operating-systems offer something similar.
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Database Size Limiting

[Richard Huxton]

Campano, Troy wrote:
Is there a way to limit the size that an individual database can take
up?
My understanding is that all your databases go into a file system which
is your 'catalog cluster'.
But all the databases can grow until that filesystem is full.
Is there a way to limit how big a database can be? Can you allocated
space when the database is set up so you can say db1 can only be 100 MB?
Well, you've got two main options:
1. Run multiple instances of PG
If you run each as its own user postgres1,postgres2 etc then you can 
use the standard user permissions to control resource usage.

2. Place each database on its own volume
Mount one volume per database, move and symlink that database to the 
volume. If you volume is only 100MB then the database will stop there.

Of course, what you probably want to do is monitor each folder (tip - 
the folder names in .../data/base are the OIDs of databases) and warn at 
75MB and disallow access if usage is 125MB for more than 24 hours. 
That's something outside of PG's control, but someone will have done it.

Oh - final option would be one of the virtual server setups which let 
you allocate resource limits per vserver.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Database Size Limiting

[Joshua D. Drake]

Hello,
You could use Filsystem quotas but I strongly suggest against it as it 
will probably bring about database corruption.

A better solution would be to monitor the space being allocated so you
can add storage as required.
Sincerely,
Joshua D. Drake
Campano, Troy wrote:
--
Is there a way to limit the size that an individual database can take up?
My understanding is that all your databases go into a file system which 
is your catalog cluster.

But all the databases can grow until that filesystem is full.
Is there a way to limit how big a database can be? Can you allocated 
space when the database is set up so you can say db1 can only be 100 MB?

 

Thank you!
*Troy Campano*

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0034
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We  provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored  the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] Database Size Limiting

[Campano, Troy]

My understanding is databases reside within 'database clusters' in
postgresql. Is this right? Can I run multiple database clusters within
one postgresql/postmaster server? Then could I use filesystem quotas for
each cluster?


Thank you!


-Original Message-
From: Joshua D. Drake [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 27, 2004 2:12 PM
To: Campano, Troy
Cc: Postgres general mailing list
Subject: Re: [GENERAL] Database Size Limiting

Hello,

You could use Filsystem quotas but I strongly suggest against it as it 
will probably bring about database corruption.

A better solution would be to monitor the space being allocated so you
can add storage as required.

Sincerely,

Joshua D. Drake


Campano, Troy wrote:
 --
 
 Is there a way to limit the size that an individual database can take
up?
 
 My understanding is that all your databases go into a file system
which 
 is your 'catalog cluster'.
 
 But all the databases can grow until that filesystem is full.
 
 Is there a way to limit how big a database can be? Can you allocated 
 space when the database is set up so you can say db1 can only be 100
MB?
 
  
 
 Thank you!
 
 *Troy Campano*
 


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] Database Size Limiting

[Richard Huxton]

Joshua D. Drake wrote:
Hello,
You could use Filsystem quotas but I strongly suggest against it as it 
will probably bring about database corruption.
I thought we handled that? I'm not saying it'd be pleasant, but I think 
it's no worse than a power-outage.

Hmm, can't find anything in the manuals to  back that up. I can find 
postings to the mailing lists though, and no-one is complaining PG ate 
their data.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Database Size Limiting

[Richard Huxton]

Joshua D. Drake wrote:
Hello,
You could use Filsystem quotas but I strongly suggest against it as it 
will probably bring about database corruption.
Ah - manual reference
http://www.postgresql.org/docs/current/static/disk-full.html
24.2. Disk Full Failure
The most important disk monitoring task of a database administrator is 
to make sure the disk doesn't grow full. A filled data disk may result 
in subsequent corruption of database indexes, but not of the tables 
themselves. If the WAL files are on the same disk (as is the case for a 
default configuration) then a filled disk during database initialization 
may result in corrupted or incomplete WAL files. This failure condition 
is detected and the database server will refuse to start up. 

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Database Size Limiting

[Campano, Troy]

Is it possible to have a database spread across two data files?
So if it fills up filesystem1 then we could add a second filesystem and
allow it to grow onto this second filesystem?


Thanks!


-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 27, 2004 3:23 PM
To: Joshua D. Drake
Cc: Campano, Troy; Postgres general mailing list
Subject: Re: [GENERAL] Database Size Limiting

Joshua D. Drake wrote:
 Hello,
 
 You could use Filsystem quotas but I strongly suggest against it as it

 will probably bring about database corruption.

Ah - manual reference
http://www.postgresql.org/docs/current/static/disk-full.html

24.2. Disk Full Failure

The most important disk monitoring task of a database administrator is 
to make sure the disk doesn't grow full. A filled data disk may result 
in subsequent corruption of database indexes, but not of the tables 
themselves. If the WAL files are on the same disk (as is the case for a 
default configuration) then a filled disk during database initialization

may result in corrupted or incomplete WAL files. This failure condition 
is detected and the database server will refuse to start up. 

-- 
   Richard Huxton
   Archonet Ltd

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Database Size

[Feite Brekeveld]

jackie wrote:

Hello
EveryBody How could I get
the size(MB) of the Database
du on the datadirectory where the database are located.
Feite