| From: | CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr> |
|---|---|
| To: | Jeremiah Bauer <jbauer(at)agristats(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Postgres Database Disk Usage |
| Date: | 2019-02-11 07:33:05 |
| Message-ID: | 804fbf8d-59a9-8a87-d7bd-fc683018b659@developpement-durable.gouv.fr |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hello,<br>
<br>
We've got postgres 9.6.11 until december 7th and sometime we have
corrupt data or older files not deleted.<br>
<br>
We have found it Two times until 9.6.11.<br>
<br>
We keep cool by restoring data in another base an delete the bigger
one.<br>
<br>
You can do this to know how cost your data :<br>
<br>
<pre><code>SELECT pg_size_pretty(sum(pg_table_size(oid))) FROM pg_class;
if your directory is bigger there's a little probleme.</code>
</pre>
<br>
<div class="moz-signature">
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 2.3 (Win32)">
<meta name="AUTHOR" content="dreal-ra">
<meta name="CREATED" content="20110608;16534339">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGED" content="20120220;8494106">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<style type="text/css">
<!--
@page { size: 21cm 29.7cm; margin: 2cm }
P { margin-bottom: 0.21cm }
-->
</style>
<pre><b>Yann Convers</b>
Gestionnaire de l'infrastructure, des référentiels et des outils<p>
<font color="#000000"><b>DREAL Auvergne-Rhône-Alpes</b></font></p>
<pre>Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : <a href="mailto:yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr">yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig(dot)dreal-ara(at)developpement-durable(dot)gouv(dot)fr">boite unité</a>
<p>Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : <a href="http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new">http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new</a></p>
<b>Adresse postale :</b> DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG
69453 LYON CEDEX 06
<b>Adresse physique :</b> 5
place Jules Ferry (immeuble Lugdunum - métro Brotteaux) -
69006 Lyon
<b>Standard :</b> 04 26 28 60 00
<b>Liens :</b><a href="http://intra.dreal-rhone-alpes.i2/">Site
Intranet</a> <a href="http://www.auvergne-rhone-alpes.developpement-durable.gouv.fr/">Site
Internet</a>
<p style="margin-bottom: 0cm">
</p>
</pre>
</div>
<div class="moz-cite-prefix">Le 08/02/2019 à 16:47, > Jeremiah
Bauer (par Internet, dépôt
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-admin-owner+m63745-121696(at)lists(dot)postgresql(dot)org">pgsql-admin-owner+m63745-121696(at)lists(dot)postgresql(dot)org</a>) a écrit :<br>
</div>
<blockquote
cite="mid:DM5PR19MB1418873B4EFF26F9CABAD90FC2690(at)DM5PR19MB1418(dot)namprd19(dot)prod(dot)outlook(dot)com"
type="cite">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
Hello,</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
We have a development database that reports a larger size than
the sum of it's relations and I was hoping someone had some
insight into this issue. The consumed space is reflected in the
size of the data directories PostgreSQL is maintaining on disk,
so it's just not a reported size issue.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
This is the version of Postgres we are using:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<span style="margin: 0px; font-family: Calibri, Arial,
Helvetica, sans-serif; background-color: rgb(255, 255, 255)">FVDM=#
select version();<br>
</span>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica,
sans-serif; background-color: rgb(255, 255, 255)">
version
<br>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica,
sans-serif; background-color: rgb(255, 255, 255)">
-----------------------------------------------------------------------------------------------------------<br>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica,
sans-serif; background-color: rgb(255, 255, 255)">
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit<br>
</div>
<span style="margin: 0px; font-family: Calibri, Arial,
Helvetica, sans-serif; background-color: rgb(255, 255, 255)"></span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
pg_database_size reports this size:<br>
<br>
<span> name | owner | size <br>
</span>
<div>------------+----------+---------<br>
</div>
<div> FVDM | postgres | 43 GB<br>
</div>
<span></span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
Running a query to report the total relation size of each schema
results in this:<br>
<br>
<span> schemaname | pg_size_pretty <br>
</span>
<div>--------------------+----------------<br>
</div>
<div> information_schema | 152 kB<br>
</div>
<div> pg_catalog | 7880 kB<br>
</div>
<div> public | 52 MB<br>
</div>
<div> mart | 439 MB<br>
</div>
<div>(4 rows)<br>
</div>
<span></span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
The total size of the relations is less than a gigabyte, but the
database is 43GB in size. I've checked each table and there
aren't any added indexes, so I'm very confused.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
We've run vacuum full and reindex on the database with no
reduction in size and a vacuum analyze verbose reports no
recoverable rows. I cloned the machine for troubleshooting and
even attempted truncating every table in the database to see if
that remedied the issue. The only thing that has reclaimed the
space is dropping the database and restoring from backup.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
What is using the space and how do we reclaim it? </div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
Any help or insight would be greatly appreciated, I'm at a loss
as to what is consuming this space.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="signature">
<div id="divtagdefaultwrapper" dir="ltr" style="font-size:12pt;
color:#000000; font-family:Calibri,Helvetica,sans-serif">
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0;
margin-bottom:0">--</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0;
margin-bottom:0"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0;
margin-bottom:0">Jeremiah</p>
</div>
</div>
CONFIDENTIALITY NOTICE: The information contained in this email
(and any attachments) is privileged and confidential and protected
from disclosure. If you are not the intended recipient of this
email or the attachments, be aware that any disclosure, copying,
distribution or use of this email or any attachment is strictly
prohibited and you should not read the message or read or open any
attachment. If you have received this email by mistake, please
immediately notify the sender and delete it permanently from your
system. Agri Stats, Inc. and its subsidiaries will not be held
liable to any person or entity resulting from the unintended or
unauthorized use of any information contained in this email.
</blockquote>
<br>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 9.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG | 2019-02-11 07:44:06 | Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage |
| Previous Message | Rohit Arora | 2019-02-10 13:35:10 | Re: ERROR: out of memory |