postgresql wal 在哪里？如何指定不同的路径？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19047954/
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
Where is the postgresql wal located? How can I specify a different path?
提问by Yves Dorfsman
I want to create a database with the data files and the wal on different filesystems. I want the wal on a separate server over NFS, to avoid a loss of data in case of a fs/disk crash.
我想在不同的文件系统上创建一个包含数据文件和 wal 的数据库。我希望 wal 位于 NFS 上的单独服务器上，以避免在 fs/磁盘崩溃时丢失数据。
Where is the wal written?
沃尔写在哪里？
Can I force it to a different location than the default via the configuration?
我可以通过配置将其强制到与默认值不同的位置吗？
I'm on 9.1 if that matters.
如果那重要的话，我在 9.1 上。
Thanks.
谢谢。
回答by a_horse_with_no_name
The WAL files are written to the directory pg_xloginside of the data directory. Starting with Postgres 10, this directory was renamed to pg_wal
WAL 文件被写入pg_xlog数据目录内的目录。从 Postgres 10 开始，这个目录被重命名为pg_wal
E.g. /var/lib/postgresql/10/main/pg_wal
例如 /var/lib/postgresql/10/main/pg_wal
See the manual for details:
详情请参阅手册：
- http://www.postgresql.org/docs/9.1/static/wal-configuration.html
- http://www.postgresql.org/docs/current/static/wal-configuration.html
- http://www.postgresql.org/docs/9.1/static/wal-configuration.html
- http://www.postgresql.org/docs/current/static/wal-configuration.html
If I'm not mistaken, this directory name can not be changed. But it can be a symbolic link that points to a different disk.
如果我没记错的话，这个目录名是不能改变的。但它可以是指向不同磁盘的符号链接。
As a matter of fact this is actually recommended to tune WAL performance (See here: http://wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices#WAL_Directory)
事实上，这实际上是推荐用于调整 WAL 性能的（参见此处：http: //wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices#WAL_Directory）
回答by technogeek1995
To Copy the WAL Directory to another file path/disk drive, follow these steps below:
要将 WAL 目录复制到另一个文件路径/磁盘驱动器，请按照以下步骤操作：
Descriptive Steps
描述步骤
- Turn off Postgres to protect against corruption
- Copy WAL directory (by default on Ubuntu -
/var/lib/postgresql/<version>/main/pg_wal) to new file path usingrsync. It will preserve file/folder permissions and folder structure with the-aflag. You should leave off the training slash. - Verify the contents copied correctly
- Rename
pg_waltopg_wal-backupin the Postgres data directory ($PG_DATA) - Create a symbolic link to the new path to
pg_walin the Postgres data directory ($PG_DATA) and update the permissions of the symbolic link to be thepostgresuser - Start Postgres and verify that you can connect to the database
- Optionally, delete the
pg_wal-backupdirectory in the Postgres data directory ($PG_DATA)
- 关闭 Postgres 以防止腐败
- 复制WAL目录（默认在Ubuntu -
/var/lib/postgresql/<version>/main/pg_wal）使用新的文件路径rsync。它将使用-a标志保留文件/文件夹权限和文件夹结构。你应该去掉训练斜线。 - 验证复制的内容是否正确
- 在 Postgres 数据目录中重命名
pg_wal为pg_wal-backup($PG_DATA) pg_wal在 Postgres 数据目录 ($PG_DATA) 中创建一个指向新路径的符号链接，并将符号链接的权限更新为postgres用户- 启动 Postgres 并验证是否可以连接到数据库
- （可选）删除
pg_wal-backupPostgres 数据目录中的目录 ($PG_DATA)
Matching Commands
匹配命令
sudo service postgresql stop
sudo rsync -av /var/lib/postgresql/12/main/pg_wal /<new_path>
ls -la /<new_path>
sudo mv /var/lib/postgresql/12/main/pg_wal /var/lib/postgresql/12/main/pg_wal-backup
sudo ln -s /wal/pg_wal /var/lib/postgresql/12/main/pg_wal
sudo chown -h postgres:postgres /var/lib/postgresql/12/main/pg_wal
sudo service postgresql start && sudo service postgresql status
# Verify DB connection using your db credentials/information
psql -h localhost -U postgres -p 5432
rm -rf /var/lib/postgresql/12/main/pg_wal-backup
