如何通过 Amazon EC2 将 postgresql 数据移动到 Ubuntu 上的另一个目录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16678872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I move postgresql data to another directory on Ubuntu over Amazon EC2?
提问by deadlock
We've been running postgresql 8.4 for quite some time. As with any database, we are slowly reaching our threshold for space. I added another 8 GB EBS drive and mounted it to our instance and configured it to work properly on a directory called /files
我们运行 postgresql 8.4 已经有一段时间了。与任何数据库一样，我们正在慢慢达到我们的空间门槛。我添加了另一个 8 GB EBS 驱动器并将其安装到我们的实例并将其配置为在名为 /files 的目录上正常工作
Within /files, I manually created
在 /files 中，我手动创建
Correct me if I'm wrong, but I believe all postgresql data is stored in /var/lib/postgresql/8.4/main
如果我错了，请纠正我，但我相信所有 postgresql 数据都存储在 /var/lib/postgresql/8.4/main 中
I backed up the database and I ran sudo /etc/init.d/postgresql stop. This stops the postgresql server. I tried to copy and paste the contents of /var/lib/postgresql/8.4/main into the /files directory but that turned out be a HUGE MESS! due to file permissions. I had to go in and chmod the contents of that folder just so that I could copy and paste them. Some files did not copy fully because of root permissions. I modified the data_directory parameter in postgresql.conf to point to the files directory
我备份了数据库并运行 sudo /etc/init.d/postgresql stop。这将停止 postgresql 服务器。我试图将 /var/lib/postgresql/8.4/main 的内容复制并粘贴到 /files 目录中，但结果是一团糟！由于文件权限。我不得不进入并修改该文件夹的内容，以便我可以复制和粘贴它们。由于 root 权限，某些文件没有完全复制。我修改了 postgresql.conf 中的 data_directory 参数指向文件目录
data_directory = '/files/postgresql/main'
and I ran sudo /etc/init.d/postgresql restart and the server failed to start. Again probably due to permission issues. Amazon EC2 only allows you to access the service as ubuntu by default. You can only access root from within the terminal which makes everything a lot more complicated.
我运行 sudo /etc/init.d/postgresql restart 并且服务器无法启动。再次可能是由于权限问题。默认情况下，Amazon EC2 仅允许您以 ubuntu 身份访问该服务。您只能从终端内访问 root，这使一切变得更加复杂。
Is there a much cleaner and more efficient step by step way of doing this?
有没有更清洁、更有效的逐步方式来做到这一点？
回答by Craig Ringer
Stop the server.
停止服务器。
Copy the datadir while retaining permissions - use cp -aRv.
在保留权限的同时复制数据目录 - 使用cp -aRv。
Then (easiest, as it avoids the need to modify initscripts) just move the old datadir aside and symlink the old path to the new location.
然后（最简单，因为它避免了修改 initscripts 的需要）只需将旧的 datadir 移到一边并将旧路径符号链接到新位置。
回答by Kenyakorn Ketsombut
Thanks for the accepted answer. Instead of the symlink you can also use a bind mount. That way it is independent from the file system. If you want to use a dedicated hard drive for the database you can also mount it normally. to the data directory.
感谢您接受的答案。除了符号链接，您还可以使用绑定安装。这样它就独立于文件系统。如果要为数据库使用专用硬盘驱动器，也可以正常挂载。到数据目录。
I did the latter. Here are my steps if someone needs a reference. I ran this as a script on many AWS instances.
我做了后者。如果有人需要参考，这是我的步骤。我在许多 AWS 实例上将此作为脚本运行。
# stop postgres server
sudo service postgresql stop
# create new filesystem in empty hard drive
sudo mkfs.ext4 /dev/xvdb
# mount it
mkdir /tmp/pg
sudo mount /dev/xvdb /tmp/pg/
# copy the entire postgres home dir content
sudo cp -a /var/lib/postgresql/. /tmp/pg
# mount it to the correct directory
sudo umount /tmp/pg
sudo mount /dev/xvdb /var/lib/postgresql/
# see if it is mounted
mount | grep postgres
# add the mount point to fstab
echo "/dev/xvdb /var/lib/postgresql ext4 rw 0 0" | sudo tee -a /etc/fstab
# when database is in use, observe that the correct disk is being used
watch -d grep xvd /proc/diskstats
回答by datasage
A clarification. It is the particular AMI that you used that sets ubuntu as the default user, this may not apply to other AMIs.
澄清。您使用的特定 AMI 将 ubuntu 设置为默认用户，这可能不适用于其他 AMI。
In essence if you are trying move data manually, you will probably need to do so as the root user, and then make sure its available to whatever user postgres is running with.
本质上，如果您尝试手动移动数据，您可能需要以 root 用户身份执行此操作，然后确保它可用于运行 postgres 的任何用户。
You also do have the option of snapshotting the volume and increasing the size of the a volume created from the snapshot. Then you could replace the volume on your instance with the new volume (You probably will have to resize the partition to take advantage of all the space).
您还可以选择对卷进行快照并增加从快照创建的卷的大小。然后，您可以用新卷替换实例上的卷（您可能必须调整分区大小以利用所有空间）。
