Ошибка записи файла базы данных (возможно, нет места на диске) или Ошибка СУБД: No space left on device
Описание ошибки:
На сервере ошибка возникла в обоих вариантах одновременно в двух базах. Только первый текст ошибки — для файловой базы, а второй — для серверной. Каталог файловой базы данных и папки СУБД сервеной базы находились на одном фиизческом диске.
Найденные решения:
Ошибка записи файла базы данных (возможно, нет места на диске) ‘К<каталог_базы>’/1Cv8tmp.1CD
Ошибка СУБД:
ERROR: could not write block 41 of temporary file: No space left on device
Формулировка текста ошибки на русском языке проявила себя быстрее в файловой базе. По формулировке ошибки в принципе понятно, что не просто возможно, а на самом деле место на жестком диске закончилось. Т.е. информирование в ошибке о недостатке физического пространства, как в русской, так и в английской формулировке «No space left on device» (На устройстве нет свободного места) — говорит об первом, что нужно сделать — проверить количество свободной памяти на жестком диске, где размещена база(ы).
Оцените, помогло ли Вам предоставленное описание решения ошибки?
© www.azhur-c.ru 2014-2020. Все права защищены. Использование текстов и изображений с данной страницы без письменного разрешения владельца запрещено. При использовании материалов с данной страницы обязательно указание ссылки на данную страницу.
06-02-2020
Журавлев А.С.
(Сайт azhur-c.ru)
I’m running a query that duplicates a very large table (92 million rows) on PostgreSQL. After a 3 iterations I got this error message:
The query was:
CREATE TABLE table_name
AS SELECT * FROM big_table
The issue isn’t due to lack of space in the database cluster: at 0.3% of max possible storage at the time of running the query, table size is about 0.01% of max storage including all replicas. I also checked temporary files and it’s not that.
Laurenz Albe
190k17 gold badges175 silver badges229 bronze badges
asked Nov 9, 2020 at 14:32
3
You are definitely running out of file system resources.
Make sure you got the size right:
SELECT pg_table_size('big_table');
Don’t forget that the files backing the new table are deleted after the error, so it is no surprise that you have lots of free space after the statement has failed.
One possibility is that you are not running out of disk space, but of free i-nodes. How to examine the free resources differs from file system to file system; for ext4 on Linux it would be
sudo dumpe2fs -h /dev/... | grep Free
answered Nov 9, 2020 at 17:55
Laurenz AlbeLaurenz Albe
190k17 gold badges175 silver badges229 bronze badges
«no space left on the device» could also mean that the partition is out of inodes. Use df -i to check inode usage.
Edit: Each file, directory, and symbolic link requires one inode. So the idea is to remove some files from the / partition. It doesn’t matter how large the files are. You can of course just pick some files on the partition and move them to another partition, if you can find some that are convenient to move.
It’s unusual to run out of inodes, so it’s possible you have some directory full of small or zero-length files somewhere that you’re not aware of. Unfortunately, I don’t know of an easy way to find this sort of thing. Running find / -xdev would print the name of every file and directory on the partition. You might be able to spot some suspicious directory full of empty files.
If you can add additional disk partitions to the system, you could pick some directory on the / partition and make it into a separate partition. Copy all of the files in that directory to the new partition, delete them from the / partition, then mount the new partition on top of the old (and now empty) directory.
The nuclear option would be to back up the / partition, wipe it, and rebuild it with more inodes. That’s an advanced topic and I won’t try to explain it here.
I’ve been inserting data into PostgreSQL DB, and got the error message:
psycopg2.OperationalError: could not extend file "base/16384/61892": No space left on device
HINT: Check free disk space
I am using CentOS 7, and I’ve checked some of my status on my linux machine:
df -h
I’m pretty new to Linux, and based on the result of my df -h command, I could see that Linux storage system works differently than Windows.
It looks like PostgreSQL is pushing to /dev/mapper/centos-root, but it has only 50 GB available.
Question 1: How can I change it? How can I assign the extra free space that’s available in /dev/mapper/centos-home to my ~/centos-root directory?
Question 2: My machine has two Hard disk, 200 GB each. But according to my df-h command, it looked like I only have 200GB available. Why is this the case? How can I take advantage of full 400GB space?
It looks like the problem is that my /dev/mapper/centos-root is all filled up. I have inserted nearly 10,000,000 rows of data with 12 columns in PostgreSQL.
Question 3: Is this big enough to take up 50GB of space?
Thanks!
Edit: output of pvs, vgs, lvs
из FAQ по postgreSQL
«4.5) Каковы максимальные размеры для записей, таблиц и базы данных?
Существуют следующие ограничения:
Максимальный размер базы? неограничен (существуют базы на 32 TB)
Максимальный размер таблицы? 32 TB
Максимальный размер записи? 1.6 TB
Максимальный размер поля? 1 GB
Максимальное количество записей в таблице? неограничено
Максимальное количество колонок в таблице? 250-1600 в зависимости от типа
Максимальное количество индексов в таблице? неограничено
Разумеется, понятие «неограничено» на самом деле ограничивается доступным дисковым пространиством и размерами памяти/своппинга. Когда значения перечисленные выше неоправдано большие, может пострадать производительность.
Максимальный размер таблицы в 32 TB не требует чтобы операционная система поддерживала файлы больших размеров. Большие таблицы хранятся как множество файлов размером в 1 GB, так что ограничения, которые накладывает файловая система не важны.
Максимальный размер таблицы и максимальное количество колонок могут быть увеличены в четыре раза, если размер блока по умолчанию будет увеличен до 32k.»
другое дело, мб где-то задалось ограничение на размер ??
где его посмотреть, не знаю 🙁
No space left on device (28) is a common error in Linux servers.
As a Server Administration Service provider, we see this error very often in VPSs, Dedicated Servers, AWS cloud instances and more.
It could happen seemingly for no reason at all (like refreshing a website), or when updating data (like backup sync, database changes, etc.).
What is the error “No space left on device (28)”?
For Linux to be able to create a file, it needs two things:
- Enough space to write that file.
- A unique identification number called “inode” (much like a social security number).
Most server owners look at and free up the disk space to resolve this error.
But what many don’t know is the secret “inode limit“.
What is “inode limit”, you ask? OK, lean in.
Linux identifies each file with a unique “inode number”, much like a Social Security number. It assigns 1 inode number for 1 file.
But each server has only a limited set of inode numbers for each disk. When it uses up all the unique inode numbers in a disk, it can’t create a new file.
Quite confusingly, it shows the error : No space left on device (28) and people go hunting for space usage issues.
Now that you know what to look for, let’s look at how to fix this issue.
In reality, there’s no single best way to resolve this error.
Our Dedicated Server Administrators maintain Linux servers of web hosts, app developers and other online service providers.
We’ve seen variations of this error in MySQL servers, PHP sites, Magento stores and even plain Linux servers.
Going over our support notes, we can say that an ideal solution that’s right for one server might cause unintended issues in another server.
So, before we go over the solutions, carefully study all options and choose the best for you.
Broadly, here are the ways in which we resolve “No space left on device (28)“:
- Deleting excess files.
- Increasing the space availability.
- Fixing service configuration.
Now let’s look at the details of each case.
Fixing Magento error session_start(): failed: No space left on device
Our Dedicated Server Administrators support several high traffic online shops that use Magento.
In a couple of these sites, we have seen the error “session_start(): failed: No space left on device” when the site traffic suddenly goes up during marketing campaigns, seasonal sales, etc.
That happens when Magento creates a lot of session and cache files to store visitor data and to speed up the site.
In all the cases, we’ve seen the inode count to be maxed out, while the was still disk space.
Here, deleting the session or cache files might look like a good idea, but those files will come back in a few minutes.
So, after we as a pemanent solution, we do these:
- Setup cache expiry and auto-cleaning – These servers uses PHP cache and Magento cache, whcih created a lot of files during high traffic hours. So, we configured cache expiry, and setup a script to clear all cache files older than 24 hours.
- Configure log rotation – Some logs grew to more than 50 GB, which posed a threat to the disk space. So, we configured the log rotate service to limit log size to 500 MB, and to delete all logs older than 1 week.
- Auto-clear session files – The session files were set to tbe auto-deleted with a custom script so that inode numbers are not used up.
- Audit disk usage periodically – On top of all this, our admins audit these servers every couple of weeks to make sure the systems are working properly, and if needed make corrections.
If you need help fixing this error in your Magento server, click here to talk to our Magento experts. We are online 24/7.
Resolving WordPress “Warning: sessionstart(): open(/tmp/mysite/tempfile) failed: No space left on device (28)”
WordPress is another popular web platform where we’ve seen this error.
When updating content or logging into the admin panel, a user sees the error:
Warning: sessionstart(): open(/tmp/mysite/tempfile) failed: No space left on device (28)
The most common reasons for this are:
- User’s space/inode quota exhaustion.
- Inode exhaustion in session directory.
- Space/Inode exhaustion in the server.
Fixing quota issues
If the site is hosted in a shared server or a VPS, there’ll be account default limits to storage space and inode counts.
In many cases, we’ve found large files (such as backup, videos, DB dumps, etc.) in the user’s home directory itself. But there are other locations that are not so obvious:
- Trash or Spam mail folders
- Catch-all mail accounts
- Web app log files (eg. WordPress error log)
- Old log files (eg. access_log.bak)
- Old uncompressed backups (from a previous site restore for eg.)
- Un-used web applications
The first step in such situations is to look at these locations, and remove all files that are not necessary. That’ll resolve the error in the website, but that is not enough.
An important part of our support philosophy is to implement fixes such that an issue won’t come back.
So, our server admins then dig in and figure out why the space exceeded in the first place.
Some common reasons and the permanent resolutions we implement are:
- Abandoned mailboxes – Unused mail accounts can easily become a spam dump, and eat up all the disk space. We set up disk usage alert mails to the site owner so that once any mail account exceeds the normal size, the account owner can investigate and delete unwanted mails or accounts.
- Unmaintained IMAP accounts – We’ve seen IMAP accounts with over 10 GB of mails in Trash. To avoid such issues, we setup fixed email quotas and alert mails so that users and site owners can take action before the quota tuns out.
- Old applications and files – In some websites we’ve seen old applications that were once used or installed to test features. These apps not only waste disk space, but are also a security threat. In the sites we maintain for our customers, we prevent such issues through periodic website/server audits where we detect and remove unused files.
Fixing inode exhaustion in session directory
WordPress plugins store session files in the home directory by default.
But some servers might have this set to an external directory like /tmp or /var depending on the application used to setup PHP and WordPress.
Since session files aren’t too large or numerous, it’ll usually be something else that’s taking up space in those directories.
For instance, in one server, the session directoy was set to /var/apps/users/path/to/tmpsession. The space in /var directory was exhausted by discarded backup dumps.
In that particular server, the solution was to delete old backups, but it can be something else in other servers.
So, we follow a top-down approach by looking at the inode count for each main folder, and then drill down to the directory that consumes the most inodes.
An example of a command used to find inode usage.
Fixing Space or Inode exhaustion in a server
This is a variation of the issue mentioned above.
Unoptimized backup processes or cache systems or log services can leave a lot of files lying around.
That’ll quickly consume the available space and inode quota.
So,
- We first sort the directories in a server based on their space and inode usage.
- Then we figure out which ones are not really needed, and delete the excess files.
- And last, we tweak the services that created those files to clear out the files after a fixed limit.
If your WordPress site or web application is currently showing this error, we can help you fix it. Click here to talk to our experts. We are online 24/7.
How to fix rsync: mkstemp “/path/to/file” no space left on device (28)
Many backup software use rsync tool to transfer backup files between servers.
We’ve seen many backup processes fail with the error rsync: mkstemp "/path/to/file" no space left on device (28).
Among the many reasons for this error, these are the most common:
- Double space issue – In its default setting, Rsync needs double the space to update a file. So, if rsync is trying to update a 20 GB file, it needs another 20 GB free space to hold the temporary file while the new version is being transferred from the source server. In some servers, this fails due to reaching space or quota limits.
- Quota exhaustion – In VPS servers there could be account level limits on number of inodes and space available. During transfer of large directories, this quota could get exhausted.
- Inode / Space limit on drive – Backup folders or database directories are sometimes mounted on a separate directory with limited space. So, when moving large archives, the space or inode limits can get exhausted.
To solve these issues, first we figure out exactly which limit is getting hit, and if there’s a way to implement an alternate solution.
For instance if we find that rsync is trying to update a large file of many GBs in size (eg. database dump or backup archive), we use an option called --inplace in the rsync command.
This will skip creating the temporary file, and will just update those parts of the file that were changed. In this way we avoid the need to upgrade disk quota.
Fixing space or inode overage
By far, the most common cause for inode or space exhaustion is all the good space being taken up by files that the server owner doesn’t need.
It could be old uncompressed backups, cache files, spam files and more.
So, the first step in our troubleshooting is always to sort all folders based on their space and inode usage.
Once we know the top folders that contributed to the disk overage, we can then drill down to weed out those that are not needed.
Here’s an example of a command that lists folders by space usage.
The second, and more important step in this resolution is to find out which process caused the junk file build-up and then reconfigure the service to automatically clean out old files.
Are you facing an rsync error right now? We can help you fix it in a few minutes. Click here to talk to our experts. We are online 24/7.
Fixing MySQL Errcode: 28 – No space left on device
MySQL servers sometimes run into this error when executing complex queries. An example is:
ERROR 3 (HY000) at line 1: Error writing file '/tmp/MY4Ei1vB' (Errcode: 28 - No space left on device)
When executing complex queries that merges several tables together, MySQL builds temporary tables in /tmp drive.
If the space or inodes are exhausted for any reason in these folders, MySQL will exit with this error.
Temporary drive can get quickly filled up with cache files, session files or other temporary files that was never cleaned up.
To resolve and prevent this from happening, we setup /tmp cleaning programs that’ll prevent junk files from piling up, and will be run every time the usage raises above 80%.
If you need help in resolving this error in your MySQL server, we can help you. Click here to talk to our experts. We are online 24/7.
How Bobcares helps prevent disk errors
All of what we have said here today is more “reactive” that “proactive”.
We said how we would RECOVER a server once a service fails with this error.
Here at Bobcares, our aim is to PREVENT such errors from happenig in the first place.
How do we do it? By being proactive.
We maintain production servers of web hosts, app developers, SaaS providers and other online businesses.
An important part in this maintenance service is Periodic Server Audits.
During audits, expert server administrators manually check every part of the server and fix everything that can affect the uptime of services.
Some of the checks to prevent disk errors are:
- Inode and Space usage – We check the available disk space and inodes in a server, and compare it with previous audit results. If we see an abnormal increase in the usage, we investigate in detail and regulate the service that’s causing the file growth.
- Temp folder auto-clearence – We setup scripts that keep the temp folders clean, and during the audits make sure that the program is working fine.
- Spam and catchall folders – We look for high volume mail directories, and delete accounts that are no longer maintained. This is has many times helped us prevent quota overage.
- Unused accounts deletion – Old user accounts that were either canceled or migrated out sometimes remain back in the server. We find and delete them.
- Old backups deletion – Sometimes people can leave uncompressed backups lying around. We detect such unused archives and delete them.
- Log file maintenance – We setup log rotation services that clears our old log files as it reaches a certain size limit. During our periodic audit we make sure these services are working well.
- Old packages deletion – Linux can leave old application installation packages even after an application is setup. We clear out old unused installation archives.
- Old application deletion – We scan for application folders that are no longer accessed, and delete unused apps and databases.
Of course, there are many more ways in which the disk space could be used up.
So, we customize the service for each of our customers, and make sure no part is left unchecked.
Over time this has helped us achieve close to 100% uptime for our customer servers.
Conclusion
No space left on device (28) is a common error in Linux servers that is caused either due to lack of disk space or due to exhaustion of inodes. Today we’ve seen the various reasons for this error to occur while using Magento, WordPress, MySQL and Rsync.
Why did I receive a «No space left on device” or «DiskFull» error on Amazon RDS for PostgreSQL?
Last updated: 2022-06-23
I have a small Amazon Relational Database Service (Amazon RDS) for PostgreSQL database. The instance’s free storage space is decreasing, and I receive the following error:
«Error message: PG::DiskFull: ERROR: could not extend file «base/16394/5139755″: No space left on device. HINT: Check free disk space.»
I want to resolve the DiskFull errors and prevent storage issues.
Short description
Amazon RDS DB instance storage is used by the following:
- Temporary tables or files that are created by PostgreSQL transactions
- Data files
- Write ahead logs (WAL logs)
- Replication slots
- DB logs (error files) that are retained for too long
- Other DB or Linux files that support the consistent state of the RDS DB instance
Resolution
1. Use Amazon CloudWatch to monitor your DB storage space using the FreeStorageSpace metric. When you set a CloudWatch alarm for free storage space, you receive a notification when the space starts to decrease. If you receive an alarm, review the causes of storage issues mentioned previously.
2. If your DB instance is still consuming more storage than expected, check for the following:
- Size of the DB log files
- Presence of temporary files
- Constant increase in transaction logs disk usage
- Replication slot:
- Physical replication slots are created by cross-Region read replicas or same-Region read replicas only if they are running on PostgreSQL 14.1 and higher versions
- Logical replication slots are created for a replica or subscriber
- Bloat or improper removal of dead rows
- Presence of orphaned files
3. When your workload is predictable, enable storage autoscaling for your instance. With storage autoscaling enabled, when Amazon RDS detects that you are running out of free database space, your storage is automatically scaled. Amazon RDS starts a storage modification for an autoscaling-enabled DB instance when the following factors apply:
- Free available space is less than 10 percent of the allocated storage.
- The low-storage condition lasts at least five minutes.
- At least six hours have passed since the last storage modification, or storage optimization has completed on the instance, whichever is longer.
You can set a limit for autoscaling your DB instance by setting the maximum storage threshold. For more information, see Managing capacity automatically with Amazon RDS storage autoscaling.
Check the size of the DB log files
By default, Amazon RDS for PostgreSQL error log files have a retention value of 4,320 minutes (three days). Large log files can use more space because of higher workloads or excessive logging. You can change the retention period for system logs using the rds.log_retention_period parameter in the DB parameter group associated with your DB instance. For example, if you set the value to 1440, then logs are retained for one day. For more information, see PostgreSQL database log files.
Also, you can change error reporting and logging parameters in the DB parameter group to reduce excessive logging. This in turn reduces the log file size. For more information, see Error reporting and logging.
Check for temporary files
Temporary files are files that are stored per backend or session connection. These files are used as a resource pool. Review temporary files statistics by running a command similar to this:
psql=> SELECT datname, temp_files AS "Temporary files",temp_bytes AS "Size of temporary files" FROM pg_stat_database ;Important: The columns temp_files and temp_bytes in view pg_stat_database are collecting statistics in aggregation (accumulative). This is by design because these counters are reset only by recovery at server start. That is, the counters are reset after an immediate shutdown, a server crash, or a point-in-time recovery (PITR). For this reason, it’s a best practice to monitor the growth of these files in number and size, rather than reviewing only the output.
Temporary files are created for sorts, hashes, or temporary query results. To track the creation of temporary tables or files, set log_temp_files in a custom parameter group. This parameter controls the logging of temporary file names and sizes. If you set the log_temp_files value to 0, then all temporary file information is logged. If you set the parameter to a positive value, then only files that are equal to or larger than the specified number of kilobytes are logged. The default setting is -1, which disables the logging of temporary files.
You can also use an EXPLAIN ANALYZE of your query to review disk sorting. When you review the log output, you can see the size of temporary files created by your query. For more information, see the PostgreSQL documentation for Monitoring database activity.
Check for a constant increase in transaction logs disk usage
The CloudWatch metric for TransactionLogsDiskUsage represents the disk space used by transaction WALs. Increases in transaction log disk usage can happen because of:
- High DB loads (writes and updates that generate additional WALs)
- Streaming read replica lag (replicas in the same Region) or read replica in storage full state
- Replication slots
Replication slots can be created as part of logical decoding feature of AWS Database Migration Service (AWS DMS). For logical replication, the slot parameter rds.logical_replication is set to 1. Replication slots retain the WAL files until the files are externally consumed by a consumer. For example, they might be consumed by pg_recvlogical; extract, transform, and load (ETL) jobs; or AWS DMS.
If you set the rds.logical_replication parameter value to 1, then AWS RDS sets the wal_level, max_wal_senders, max_replication_slots, and max_connections parameters. Changing these parameters can increase WAL generation. It’s a best practice to set the rds.logical_replication parameter only when you are using logical slots. If this parameter is set to 1 and logical replication slots are present but there isn’t a consumer for the WAL files retained by the replication slot, then then transaction logs disk usage can increase. This also results in a constant decrease in free storage space.
Run this query to confirm the presence and size of replication slots:
PostgreSQL v9:
psql=> SELECT slot_name, pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),restart_lsn)) AS
replicationSlotLag, active FROM pg_replication_slots ;PostgreSQL v10 and later:
psql=> SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag,
active FROM pg_replication_slots ;After you identify the replication slot that isn’t being consumed (with an active state that is False), drop the replication slot by running this query:
psql=> SELECT pg_drop_replication_slot('Your_slotname_name');Note: If an AWS DMS task is the consumer and it is no longer required, then delete the task and manually drop the replication slot.
Sample output:
slot_name | replicationslotlag | active
---------------------------------------------------------------+--------------------+--------
xc36ujql35djp_00013322_907c1e0a_9f8b_4c13_89ea_ef0ea1cf143d | 129 GB | f
7pajuy7htthd7sqn_00013322_a27bcebf_7d0f_4124_b336_92d0fb9f5130 | 704 MB | t
zp2tkfo4ejw3dtlw_00013322_03e77862_689d_41c5_99ba_021c8a3f851a | 624 MB | tIn this example, the slot name xc36ujql35djp_00013322_907c1e0a_9f8b_4c13_89ea_ef0ea1cf143d has an active state that is False. So this slot isn’t actively used, and the slot is contributing to 129 GB of transaction files.
Drop the query by running the following command:
psql=> SELECT pg_drop_replication_slot('xc36ujql35djp_00013322_907c1e0a_9f8b_4c13_89ea_ef0ea1cf143d');Check the status of cross-Region read replicas
When you use cross-Region read replication, a physical replication slot is created on the primary instance. If the cross-Region read replica fails, then the storage space on the primary DB instance can be affected. This happens because the WAL files aren’t replicated over to the read replica. You can use CloudWatch metrics, Oldest Replication Slot Lag, and Transaction Logs Disk Usage to determine how far behind the most lagging replica is. You can also see how much storage is used for WAL data.
To check the status of cross-Region read replica, use query pg_replication_slots. For more information, see the PostgreSQL documentation for pg_replication_slots. If the active state is returned as false, then the slot is not currently used for replication.
psql=> SELECT * FROM pg_replication_slots;You can also use view pg_stat_replication on the source instance to check the statistics for the replication. For more information, see the PostgreSQL documentation for pg_stat_replication.
Check for bloat or improper removal of dead rows (tuples)
In normal PostgreSQL operations, tuples that are deleted or made obsolete by an UPDATE aren’t removed from their table. For Multi-Version Concurrency Control (MVCC) implementations, when a DELETE operation is performed the row isn’t immediately removed from the data file. Instead, the row is marked as deleted by setting the xmax field in a header. Updates mark rows for deletion first, and then carry out an insert operation. This allows concurrency with minimal locking between the different transactions. As a result, different row versions are kept as part of MVCC process.
If dead rows aren’t cleaned up, they can stay in the data files but remain invisible to any transaction, which impacts disk space. If a table has many DELETE and UPDATE operations, then the dead tuples might use a large amount of disk space that’s sometimes called «bloat» in PostgreSQL.
The VACUUM operation can free the storage used by dead tuples so that it can be reused, but this doesn’t release the free storage to the filesystem. Running VACUUM FULL releases the storage to the filesystem. Note, however, that during the time of the VACUUM FULL run an access exclusive lock is held on the table. This method also requires extra disk space because it writes a new copy of the table and doesn’t release the old copy until the operation is complete. It’s a best practice to use this method only when you must reclaim a significant amount of space from within the table. It’s also a best practice to perform periodic vacuum or autovacuum operations on tables that are updated frequently. For more information, see the PostgreSQL documentation for VACUUM.
To check for the estimated number of dead tuples, use the pg_stat_all_tables view. For more information, see the PostgreSQL documentation for pg_stat_all_tables view. In this example, there are 1999952 dead tuples (n_dead_tup):
psql => SELECT * FROM pg_stat_all_tables WHERE relname='test';
-[ RECORD 1 ]-------+------------------------------
relid | 16395
schemaname | public
relname | test
seq_scan | 3
seq_tup_read | 5280041
idx_scan |
idx_tup_fetch |
n_tup_ins | 2000000
n_tup_upd | 0
n_tup_del | 3639911
n_tup_hot_upd | 0
n_live_tup | 1635941
n_dead_tup | 1999952
n_mod_since_analyze | 3999952
last_vacuum |
last_autovacuum | 2018-08-16 04:49:52.399546+00
last_analyze | 2018-08-09 09:44:56.208889+00
last_autoanalyze | 2018-08-16 04:50:22.581935+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 1
autoanalyze_count | 1
psql => VACUUM TEST;Check for orphaned files
Orphaned files can occur when the files are present in the database directory but there are no objects that point to those files. This might happen if your instance runs out of storage or the engine crashes during an operation such as ALTER TABLE, VACUUM FULL, or CLUSTER. To check for orphaned files, follow these steps:
1. Log in to PostgreSQL in each database.
2. Run these queries to assess the used and real sizes.
# Size of the database occupied by files
psql=> SELECT pg_size_pretty(pg_database_size('DATABASE_NAME'));
# Size of database retrieved by summing the objects (real size)
psql=> SELECT pg_size_pretty(SUM(pg_relation_size(oid))) FROM pg_class;3. Note the results. If the difference is significant, then orphaned files might be using storage space.
Did this article help?
Do you need billing or technical support?
AWS support for Internet Explorer ends on 07/31/2022. Supported browsers are Chrome, Firefox, Edge, and Safari.
Learn more »