整数超出范围，剩余磁盘空间太小，无法将id转换为bigint和其他解决方案 [英] integer out of range and remaining disk space too small to convert id to bigint and other solutions
问题描述
当我插入时，我得到的整数超出范围，因为我的ID /主键被错误地创建为 int 代替 bigint 或 bigserial 。我试过了：
When I insert I am getting integer out of range because my id/primary key was mistakenly created as an int instead of a bigint or bigserial. I tried:
ALTER TABLE tbl ALTER COLUMN id TYPE BIGINT;
但是我收到以下错误，因为我的可用磁盘空间不足。
But I get the following error because my free disk space isn't big enough.
错误：无法扩展文件 base / 16401/3275205：设备
上没有剩余空间提示：检查可用磁盘空间。
SQL状态：53100
由于令人沮丧的原因，我现在无法添加磁盘空间
I can't increase the disk space right now, for frustrating reasons I won't go into.
我也尝试通过重新启动ID（我从此表中删除了很多记录，因此存在很大差距）来启动我的 seq 结束：
https：// dba。 stackexchange.com/questions/111823/compacting-a-sequence-in-postgresql
I also tried reusing the ids (I delete a lot of records from this table so there are big gaps) by doing these to start my seq over:
https://dba.stackexchange.com/questions/111823/compacting-a-sequence-in-postgresql
但对于该链接中的解决方案1：
我认为我没有磁盘空间。该表为117GB， ... data / base 中有大约24GB可用空间。我确实有150GB的可用空间来存储我的临时文件（不同的装载），这不是默认配置，但是这样做是为了在 ... data / base <中节省数据库存储空间。 / code>。如果我可以在Temp文件位置创建表，那可能会起作用，但我不知道该怎么做。
But for solution #1 in that link:
I assume I don't have the disk space. The table is 117GB and I have about 24GB available in ...data/base. I do have 150GB available where my temp files are being stored (a different mount), which is not the default configuration, but was done so I could conserve space for database storage in ...data/base. If I could create the table in Temp file location, that might work, but I don't know how to do that.
其中的解决方案2链接：
当我进入更新部分时，我在pgAdmin4中得到了这一点：
for solution #2 in that link:
When I get to the update part, I get this in pgAdmin4:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<title>500 Internal Server Error</title>
<h1>Internal Server Error</h1>
<p>The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.</p>
但是当我运行时查询仍在运行：
However the query is still running when I run:
从pg_stat_activity中选择pid，query，state，wait_event，*，其中state<> 'idle'
对于失败的 update 查询，我没有服务器日志
And I get no server log for the update query that fails.
我最终杀死了该更新查询，以为它最终还是会失败。 （除非别人有更好的主意，否则我将再次运行此脚本，并使其在html错误中运行。）
I eventually killed that update query thinking that it would fail eventually anyway. (I am running this one again and will let it run with html error above unless someone else has a better idea.)
该链接中的解决方案＃3 ：
我有16GM的RAM，所以还不够。
for solution #3 in that link: I have 16GM of RAM, so not enough.
接下来是这里：
如何在postgres中重置序列并用新数据填充id列？
我尝试了此操作
UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;
错误：整数超出范围
这会在您尝试插入时创建重复的密钥：
this creates a duplicate key when you try to insert:
ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');
还有什么我可以尝试的吗？
Anything else I can try?
PostgreSQL 9.6
PostgreSQL 9.6
推荐答案
Scott Marlowe和Vao Tsun的注释起作用：
Scott Marlowe and Vao Tsun comments worked:
在（Linux）服务器上，打开一个终端
on (linux) server open a terminal
导航到想要新命名空间的位置
navigate to where want a the new namespace to be
目录： mkdir目录名
将所有权授予postgres： chown postgres：postgres目录名
give ownership to postgres: chown postgres:postgres dirname
创建表：创建表空间new_tbl_space位置'/ path / dirname'
将表放入表空间： alter table tbl set tablespace'/ path / dirname'
做那些占用这么多磁盘空间的事情： ALTER TABLE tbl ALTER COLUMN id TYPE BIGINT;
do what was taking up so much disk space:ALTER TABLE tbl ALTER COLUMN id TYPE BIGINT;
改回表空间：更改表tbl设置表空间pg_default
删除表空间：我做到了在表空间节点/对象
remove the tablespace: I did that in pgadmin4 in the Tablespaces node/object
（那是从内存中获得的。让我知道我是否错过了什么。）
(That was from memory. Let me know if I missed something.)
编辑：这具有重写整个表的副作用，就像完全真空一样，释放了任何死磁盘空间。
This has the side effect of rewriting the entire table like a full vacuum freeing up any dead disk space.
这篇关于整数超出范围，剩余磁盘空间太小，无法将id转换为bigint和其他解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
