1. View the current data (or insert new data) as a marker.
highgo=# create table t1(id int,name text);
CREATE TABL
id | name
----+----------------------------------
1 | cf9920dd1f8a7be7e56a85f8a3e018f6
2 | cf9920dd1f8a7be7e56a85f8a3e018f6
3 | cf9920dd1f8a7be7e56a85f8a3e018f6
4 | cf9920dd1f8a7be7e56a85f8a3e018f6
5 | cf9920dd1f8a7be7e56a85f8a3e018f6
6 | cf9920dd1f8a7be7e56a85f8a3e018f6
7 | cf9920dd1f8a7be7e56a85f8a3e018f6
8 | cf9920dd1f8a7be7e56a85f8a3e018f6
9 | cf9920dd1f8a7be7e56a85f8a3e018f6
10 | cf9920dd1f8a7be7e56a85f8a3e018f6
11 | cf9920dd1f8a7be7e56a85f8a3e018f6
12 | cf9920dd1f8a7be7e56a85f8a3e018f6
13 | cf9920dd1f8a7be7e56a85f8a3e018f6
14 | cf9920dd1f8a7be7e56a85f8a3e018f6
15 | cf9920dd1f8a7be7e56a85f8a3e018f6
16 | cf9920dd1f8a7be7e56a85f8a3e018f6
17 | cf9920dd1f8a7be7e56a85f8a3e018f6
18 | cf9920dd1f8a7be7e56a85f8a3e018f6
19 | cf9920dd1f8a7be7e56a85f8a3e018f6
20 | cf9920dd1f8a7be7e56a85f8a3e018f6
(20 Line record)
highgo=# show data_directory;
data_directory
-------------------------------
D:/highgo/database/4.1.1/data
(1 Line record)
2. View the WAL log that is currently being written.
highgo=# select * from pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000002
(1 Line record)
3. Shut down the database, shut down the database service.
4. Modify the configuration file in the data directory postgresql.conf
data_directory = 'F:/data'
(be sure to note that under windows, the slash will not be recognized, and an error will be reported in the later stage.)
5. Copy the data directory to the target path
6. Start database pg_ctl start
7. Query validation
highgo=# show data_directory;
data_directory
----------------
F:/data
(1 Line record)
highgo=# select * from pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000002
(1 Line record)
//Query lost data:
select pg_xlog_location_diff('1/911974A8','1/91197440');
highgo=# select * from t1;
id | name
----+----------------------------------
1 | cf9920dd1f8a7be7e56a85f8a3e018f6
2 | cf9920dd1f8a7be7e56a85f8a3e018f6
3 | cf9920dd1f8a7be7e56a85f8a3e018f6
4 | cf9920dd1f8a7be7e56a85f8a3e018f6
5 | cf9920dd1f8a7be7e56a85f8a3e018f6
6 | cf9920dd1f8a7be7e56a85f8a3e018f6
7 | cf9920dd1f8a7be7e56a85f8a3e018f6
8 | cf9920dd1f8a7be7e56a85f8a3e018f6
9 | cf9920dd1f8a7be7e56a85f8a3e018f6
10 | cf9920dd1f8a7be7e56a85f8a3e018f6
11 | cf9920dd1f8a7be7e56a85f8a3e018f6
12 | cf9920dd1f8a7be7e56a85f8a3e018f6
13 | cf9920dd1f8a7be7e56a85f8a3e018f6
14 | cf9920dd1f8a7be7e56a85f8a3e018f6
15 | cf9920dd1f8a7be7e56a85f8a3e018f6
16 | cf9920dd1f8a7be7e56a85f8a3e018f6
17 | cf9920dd1f8a7be7e56a85f8a3e018f6
18 | cf9920dd1f8a7be7e56a85f8a3e018f6
19 | cf9920dd1f8a7be7e56a85f8a3e018f6
20 | cf9920dd1f8a7be7e56a85f8a3e018f6
(20 Line record)
8. Delete the original service and add a new service.
Must be in the cmd window with administrator privileges
Delete service:
pg_ctl unregister -N hgdb-se4.1.1
Add services:
pg_ctl register -N hgdb-se4.1.1 -U "NT AUTHORITY\NetworkService" -D "F:\data" -s
9. Modify the original data directory name
Modify or delete (strongly recommended) the original data directory name
BY sea is boundless