PostgreSQL大表复制报错：设备无剩余空间，集群空间充足仍异常

The Problem #

I tried copying a massive 92-million-row table using the standard CREATE TABLE AS SELECT statement:

CREATE TABLE table_name AS SELECT * FROM big_table

After 3 failed attempts, I kept hitting the error:

could not extend file no space left on device

Here's what I already verified:

• The database cluster has plenty of free space—only 0.3% of the maximum available storage was in use during the query
• The total size of the target table (including all replicas) is only ~0.01% of the maximum storage
• I've ruled out temporary files causing space issues

Possible Causes & Step-by-Step Fixes #

1. Specific Tablespace Partition is Full

PostgreSQL uses tablespaces to store table data; if you don't specify one, it defaults to pg_default. It's entirely possible the disk partition for this specific tablespace is full, even if the overall cluster has plenty of space left.

How to diagnose:

• Check your default tablespace:

  SHOW default_tablespace;
  

• Get the tablespace's filesystem path and check its disk usage:

  SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace WHERE spcname = 'pg_default';
  

  Run this in your terminal to check the partition:

  df -h /path/to/your/tablespace
  

Fix: Create the table in a different tablespace with available space:

CREATE TABLE table_name TABLESPACE your_other_tablespace AS SELECT * FROM big_table;

2. WAL Logs Are Filling Their Partition

Copying 92 million rows generates a huge amount of Write-Ahead Log (WAL) data. If the disk partition holding your WAL logs is full, it'll trigger this error even if the main data partition has space.

How to diagnose:

• Locate your WAL directory:

  SHOW wal_directory;
  

• Check the partition's free space:

  df -h /path/to/wal_directory
  

Fixes:

• Add space to the WAL partition, or clean up old WAL logs (only if you have archiving set up and logs are already safely archived)
• Temporarily adjust WAL-related configs (like wal_buffers or checkpoint_timeout) to reduce log volume—just make sure you understand the performance implications before changing these settings!

3. PostgreSQL User Hit Disk Quota

If PostgreSQL runs under a user account with a disk quota (common in cloud containers or restricted server environments), the process might have hit its quota even if the total disk has space left.

How to diagnose:

• Check the quota for the PostgreSQL user (usually postgres):

  quota -u postgres
  

Fix: Ask your system administrator to increase the quota for the PostgreSQL user, or migrate the database directories to a partition without quota restrictions.

4. Filesystem Ran Out of Inodes

Sometimes a filesystem has plenty of free space, but it's exhausted inodes—the data structures that track individual files. This often happens if the filesystem is cluttered with thousands of small files.

How to diagnose:

• Check inode usage for your PostgreSQL data directory:

  df -i /path/to/postgres/data
  

Fix: Clean up any unnecessary small files in the directory, or mount a new filesystem with more inodes as a dedicated tablespace.

Wrap-Up #

When you get a "no space left" error but the cluster has plenty of room, it's almost never the total cluster space—it's a specific partition, quota, or inode issue. Work through the checks above, and you should pinpoint the problem quickly.

内容的提问来源于stack exchange，提问作者haitham