Migration of DATA directory under Windows

Keywords: Database PostgreSQL Windows

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

Posted by conman90 on Tue, 26 May 2020 08:43:53 -0700