By default Red Hat and Centos OS stores the database in the file system /var/lib/pgsql/data/. The area where the database is located can be changed depending on individual environment requirements or preferences. So to speak, in this post I will show how to change this location.
First, let’s check the current location of the PostgreSQL using the command “SHOW data_directory;” inside one of the databases.
[root@techdatabasket ~]# sudo su - postgres
Last login: Thu Mar 19 15:32:14 CET 2020 on pts/1
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
-bash-4.2$ psql postgres
psql (9.5.21)
Type "help" for help.
postgres=# SHOW data_directory;
data_directory
----------------
/var/lib/pgsql/9.5/data
(1 row)
You can see in the example above that the location of the database files are the default location for RedHat which is “/var/lib/pgsql/9.5/data “. So how can we change that?
First let’s check the process of the database:
[root@techdatabasket ~]# ps -ef|grep postgres postgres 8193 29408 0 11:56 pts/1 00:00:00 vi postgresql.conf root 18009 15914 0 13:37 pts/2 00:00:00 grep --color=auto postgres root 28187 6253 0 Mar19 pts/0 00:00:00 su - postgres postgres 28189 28187 0 Mar19 pts/0 00:00:00 -bash postgres 28875 1 0 Mar19 pts/0 00:00:01 /usr/pgsql-9.5/bin/postgres -D /var/lib/pgsql/9.5/data postgres 28876 28875 0 Mar19 ? 00:00:00 postgres: logger process postgres 28878 28875 0 Mar19 ? 00:00:00 postgres: checkpointer process postgres 28879 28875 0 Mar19 ? 00:00:00 postgres: writer process postgres 28880 28875 0 Mar19 ? 00:00:00 postgres: wal writer process postgres 28881 28875 0 Mar19 ? 00:00:01 postgres: autovacuum launcher process postgres 28882 28875 0 Mar19 ? 00:00:00 postgres: archiver process last was 000000010000000000000007.00000028.backup postgres 28883 28875 0 Mar19 ? 00:00:03 postgres: stats collector process root 29407 18534 0 Mar19 pts/1 00:00:00 su - postgres postgres 29408 29407 0 Mar19 pts/1 00:00:00 -bash
Stop the cluster of the PostgreSQL:
-bash-4.2$ /usr/pgsql-9.5/bin/pg_ctl stop -D $PGDATA waiting for server to shut down.... done server stopped -bash-4.2$
Copy the files from the old directory /var/lib/pgsql/9.5/data / to the new directory /postgres/data using the utility rsync:
-bash-4.2$ rsync -av /var/lib/pgsql/9.5/data /postgres/data sending incremental file list data/ data/PG_VERSION data/pg_hba.conf data/pg_ident.conf data/postgresql.auto.conf data/postgresql.conf data/postmaster.opts data/base/ data/base/1/ data/base/1/112 data/base/1/113 data/base/1/1247 data/base/1/1247_fsm data/base/1/1247_vm data/base/1/1249 data/base/1/1249_fsm data/base/1/1249_vm data/base/1/1255 data/base/1/1255_fsm data/base/1/1255_vm data/base/1/1259 data/base/1/1259_fsm data/base/1/1259_vm data/base/1/13128 data/base/1/13128_fsm data/base/1/13128_vm data/base/1/13130 data/base/1/13132 data/base/1/13133 data/base/1/13133_fsm data/base/1/13133_vm data/base/1/13135 data/base/1/13137 data/base/1/13138 data/base/1/13138_fsm data/base/1/13138_vm data/base/1/13140 data/base/1/13142 data/base/1/13143 data/base/1/13143_fsm data/base/1/13143_vm data/base/1/13145 data/base/1/13147 data/base/1/13148 data/base/1/13148_fsm data/base/1/13148_vm data/base/1/13150 data/base/1/13152 data/base/1/13153 data/base/1/13153_fsm data/base/1/13153_vm data/base/1/13155 data/base/1/13157 data/base/1/13158 data/base/1/13160 data/base/1/13162 data/base/1/1417 data/base/1/1417_vm data/base/1/1418 data/base/1/1418_vm data/base/1/174 data/base/1/175 data/base/1/2187 data/base/1/2328 data/base/1/2328_vm data/base/1/2336 data/base/1/2336_vm data/base/1/2337 data/base/1/2600 data/base/1/2600_fsm data/base/1/2600_vm data/base/1/2601 data/base/1/2601_fsm data/base/1/2601_vm data/base/1/2602 data/base/1/2602_fsm data/base/1/2602_vm data/base/1/2603 data/base/1/2603_fsm data/base/1/2603_vm data/base/1/2604 data/base/1/2604_vm data/base/1/2605 data/base/1/2605_fsm data/base/1/2605_vm data/base/1/2606 data/base/1/2606_fsm data/base/1/2606_vm data/base/1/2607 data/base/1/2607_fsm data/base/1/2607_vm data/base/1/2608 data/base/1/2608_fsm data/base/1/2608_vm data/base/1/2609 data/base/1/2609_fsm data/base/1/2609_vm data/base/1/2610 data/base/1/2610_fsm data/base/1/2610_vm data/base/1/2611 data/base/1/2611_vm data/base/1/2612 data/base/1/2612_fsm data/base/1/2612_vm data/base/1/2613 data/base/1/2613_vm data/base/1/2615 data/base/1/2615_fsm data/base/1/2615_vm data/base/1/2616 data/base/1/2616_fsm data/base/1/2616_vm data/base/1/2617 data/base/1/2617_fsm data/base/1/2617_vm data/base/1/2618 data/base/1/2618_fsm data/base/1/2618_vm data/base/1/2619 data/base/1/2619_fsm data/base/1/2619_vm data/base/1/2620 data/base/1/2620_vm data/base/1/2650 data/base/1/2651 data/base/1/2652 data/base/1/2653 data/base/1/2654 data/base/1/2655 data/base/1/2656 data/base/1/2657 data/base/1/2658 data/base/1/2659 data/base/1/2660 data/base/1/2661 data/base/1/2662 data/base/1/2663 data/base/1/2664 data/base/1/2665 data/base/1/2666 data/base/1/2667 data/base/1/2668 data/base/1/2669 data/base/1/2670 data/base/1/2673 data/base/1/2674 data/base/1/2675 data/base/1/2678 data/base/1/2679 data/base/1/2680 data/base/1/2681 data/base/1/2682 data/base/1/2683 data/base/1/2684 data/base/1/2685 data/base/1/2686 data/base/1/2687 data/base/1/2688 data/base/1/2689 data/base/1/2690 data/base/1/2691 data/base/1/2692 data/base/1/2693 data/base/1/2696 data/base/1/2699 data/base/1/2701 data/base/1/2702 data/base/1/2703 data/base/1/2704 data/base/1/2753 data/base/1/2753_fsm data/base/1/2753_vm data/base/1/2754 data/base/1/2755 data/base/1/2756 data/base/1/2757 data/base/1/2830 data/base/1/2830_vm data/base/1/2831 data/base/1/2832 data/base/1/2832_vm data/base/1/2833 data/base/1/2834 data/base/1/2834_vm data/base/1/2835 data/base/1/2836 data/base/1/2836_vm data/base/1/2837 data/base/1/2838 data/base/1/2838_fsm data/base/1/2838_vm data/base/1/2839 data/base/1/2840 data/base/1/2840_fsm data/base/1/2840_vm data/base/1/2841 data/base/1/2995 data/base/1/2995_vm data/base/1/2996 data/base/1/3079 data/base/1/3079_fsm data/base/1/3079_vm data/base/1/3080 data/base/1/3081 data/base/1/3085 data/base/1/3118 data/base/1/3118_vm data/base/1/3119 data/base/1/3164 data/base/1/3256 data/base/1/3256_vm data/base/1/3257 data/base/1/3258 data/base/1/3455 data/base/1/3456 data/base/1/3456_fsm data/base/1/3456_vm data/base/1/3466 data/base/1/3466_vm data/base/1/3467 data/base/1/3468 data/base/1/3501 data/base/1/3501_vm data/base/1/3502 data/base/1/3503 data/base/1/3534 data/base/1/3541 data/base/1/3541_fsm data/base/1/3541_vm data/base/1/3542 data/base/1/3574 data/base/1/3575 data/base/1/3576 data/base/1/3576_vm data/base/1/3596 data/base/1/3596_vm data/base/1/3597 data/base/1/3598 data/base/1/3598_vm data/base/1/3599 data/base/1/3600 data/base/1/3600_fsm data/base/1/3600_vm data/base/1/3601 data/base/1/3601_fsm data/base/1/3601_vm data/base/1/3602 data/base/1/3602_fsm data/base/1/3602_vm data/base/1/3603 data/base/1/3603_fsm data/base/1/3603_vm data/base/1/3604 data/base/1/3605 data/base/1/3606 data/base/1/3607 data/base/1/3608 data/base/1/3609 data/base/1/3712 data/base/1/3764 data/base/1/3764_fsm data/base/1/3764_vm data/base/1/3766 data/base/1/3767 data/base/1/548 data/base/1/549 data/base/1/826 data/base/1/826_vm data/base/1/827 data/base/1/828 data/base/1/PG_VERSION data/base/1/pg_filenode.map data/base/13292/ data/base/13292/112 data/base/13292/113 data/base/13292/1247 data/base/13292/1247_fsm data/base/13292/1247_vm data/base/13292/1249 data/base/13292/1249_fsm data/base/13292/1249_vm data/base/13292/1255 data/base/13292/1255_fsm data/base/13292/1255_vm data/base/13292/1259 data/base/13292/1259_fsm data/base/13292/1259_vm data/base/13292/13128 data/base/13292/13128_fsm data/base/13292/13128_vm data/base/13292/13130 data/base/13292/13132 data/base/13292/13133 data/base/13292/13133_fsm data/base/13292/13133_vm data/base/13292/13135 data/base/13292/13137 data/base/13292/13138 data/base/13292/13138_fsm data/base/13292/13138_vm data/base/13292/13140 data/base/13292/13142 data/base/13292/13143 data/base/13292/13143_fsm data/base/13292/13143_vm data/base/13292/13145 data/base/13292/13147 data/base/13292/13148 data/base/13292/13148_fsm data/base/13292/13148_vm data/base/13292/13150 data/base/13292/13152 data/base/13292/13153 data/base/13292/13153_fsm data/base/13292/13153_vm data/base/13292/13155 data/base/13292/13157 data/base/13292/13158 data/base/13292/13160 data/base/13292/13162 data/base/13292/1417 data/base/13292/1417_vm data/base/13292/1418 data/base/13292/1418_vm data/base/13292/174 data/base/13292/175 data/base/13292/2187 data/base/13292/2328 data/base/13292/2328_vm data/base/13292/2336 data/base/13292/2336_vm data/base/13292/2337 data/base/13292/2600 data/base/13292/2600_fsm data/base/13292/2600_vm data/base/13292/2601 data/base/13292/2601_fsm data/base/13292/2601_vm data/base/13292/2602 data/base/13292/2602_fsm data/base/13292/2602_vm data/base/13292/2603 data/base/13292/2603_fsm data/base/13292/2603_vm data/base/13292/2604 data/base/13292/2604_vm data/base/13292/2605 data/base/13292/2605_fsm data/base/13292/2605_vm data/base/13292/2606 data/base/13292/2606_fsm data/base/13292/2606_vm data/base/13292/2607 data/base/13292/2607_fsm data/base/13292/2607_vm data/base/13292/2608 data/base/13292/2608_fsm data/base/13292/2608_vm data/base/13292/2609 data/base/13292/2609_fsm data/base/13292/2609_vm data/base/13292/2610 data/base/13292/2610_fsm data/base/13292/2610_vm data/base/13292/2611 data/base/13292/2611_vm data/base/13292/2612 data/base/13292/2612_fsm data/base/13292/2612_vm data/base/13292/2613 data/base/13292/2613_vm data/base/13292/2615 data/base/13292/2615_fsm data/base/13292/2615_vm data/base/13292/2616 data/base/13292/2616_fsm data/base/13292/2616_vm data/base/13292/2617 data/base/13292/2617_fsm data/base/13292/2617_vm data/base/13292/2618 data/base/13292/2618_fsm data/base/13292/2618_vm data/base/13292/2619 data/base/13292/2619_fsm data/base/13292/2619_vm data/base/13292/2620 data/base/13292/2620_vm data/base/13292/2650 data/base/13292/2651 data/base/13292/2652 data/base/13292/2653 data/base/13292/2654 data/base/13292/2655 data/base/13292/2656 data/base/13292/2657 data/base/13292/2658 data/base/13292/2659 data/base/13292/2660 data/base/13292/2661 data/base/13292/2662 data/base/13292/2663 data/base/13292/2664 data/base/13292/2665 data/base/13292/2666 data/base/13292/2667 data/base/13292/2668 data/base/13292/2669 data/base/13292/2670 data/base/13292/2673 data/base/13292/2674 data/base/13292/2675 data/base/13292/2678 data/base/13292/2679 data/base/13292/2680 data/base/13292/2681 data/base/13292/2682 data/base/13292/2683 data/base/13292/2684 data/base/13292/2685 data/base/13292/2686 data/base/13292/2687 data/base/13292/2688 data/base/13292/2689 data/base/13292/2690 data/base/13292/2691 data/base/13292/2692 data/base/13292/2693 data/base/13292/2696 data/base/13292/2699 data/base/13292/2701 data/base/13292/2702 data/base/13292/2703 data/base/13292/2704 data/base/13292/2753 data/base/13292/2753_fsm data/base/13292/2753_vm data/base/13292/2754 data/base/13292/2755 data/base/13292/2756 data/base/13292/2757 data/base/13292/2830 data/base/13292/2830_vm data/base/13292/2831 data/base/13292/2832 data/base/13292/2832_vm data/base/13292/2833 data/base/13292/2834 data/base/13292/2834_vm data/base/13292/2835 data/base/13292/2836 data/base/13292/2836_vm data/base/13292/2837 data/base/13292/2838 data/base/13292/2838_fsm data/base/13292/2838_vm data/base/13292/2839 data/base/13292/2840 data/base/13292/2840_fsm data/base/13292/2840_vm data/base/13292/2841 data/base/13292/2995 data/base/13292/2995_vm data/base/13292/2996 data/base/13292/3079 data/base/13292/3079_fsm data/base/13292/3079_vm data/base/13292/3080 data/base/13292/3081 data/base/13292/3085 data/base/13292/3118 data/base/13292/3118_vm data/base/13292/3119 data/base/13292/3164 data/base/13292/3256 data/base/13292/3256_vm data/base/13292/3257 data/base/13292/3258 data/base/13292/3455 data/base/13292/3456 data/base/13292/3456_fsm data/base/13292/3456_vm data/base/13292/3466 data/base/13292/3466_vm data/base/13292/3467 data/base/13292/3468 data/base/13292/3501 data/base/13292/3501_vm data/base/13292/3502 data/base/13292/3503 data/base/13292/3534 data/base/13292/3541 data/base/13292/3541_fsm data/base/13292/3541_vm data/base/13292/3542 data/base/13292/3574 data/base/13292/3575 data/base/13292/3576 data/base/13292/3576_vm data/base/13292/3596 data/base/13292/3596_vm data/base/13292/3597 data/base/13292/3598 data/base/13292/3598_vm data/base/13292/3599 data/base/13292/3600 data/base/13292/3600_fsm data/base/13292/3600_vm data/base/13292/3601 data/base/13292/3601_fsm data/base/13292/3601_vm data/base/13292/3602 data/base/13292/3602_fsm data/base/13292/3602_vm data/base/13292/3603 data/base/13292/3603_fsm data/base/13292/3603_vm data/base/13292/3604 data/base/13292/3605 data/base/13292/3606 data/base/13292/3607 data/base/13292/3608 data/base/13292/3609 data/base/13292/3712 data/base/13292/3764 data/base/13292/3764_fsm data/base/13292/3764_vm data/base/13292/3766 data/base/13292/3767 data/base/13292/548 data/base/13292/549 data/base/13292/826 data/base/13292/826_vm data/base/13292/827 data/base/13292/828 data/base/13292/PG_VERSION data/base/13292/pg_filenode.map data/base/13297/ data/base/13297/112 data/base/13297/113 data/base/13297/1247 data/base/13297/1247_fsm data/base/13297/1247_vm data/base/13297/1249 data/base/13297/1249_fsm data/base/13297/1249_vm data/base/13297/1255 data/base/13297/1255_fsm data/base/13297/1255_vm data/base/13297/1259 data/base/13297/1259_fsm data/base/13297/1259_vm data/base/13297/13128 data/base/13297/13128_fsm data/base/13297/13128_vm data/base/13297/13130 data/base/13297/13132 data/base/13297/13133 data/base/13297/13133_fsm data/base/13297/13133_vm data/base/13297/13135 data/base/13297/13137 data/base/13297/13138 data/base/13297/13138_fsm data/base/13297/13138_vm data/base/13297/13140 data/base/13297/13142 data/base/13297/13143 data/base/13297/13143_fsm data/base/13297/13143_vm data/base/13297/13145 data/base/13297/13147 data/base/13297/13148 data/base/13297/13148_fsm data/base/13297/13148_vm data/base/13297/13150 data/base/13297/13152 data/base/13297/13153 data/base/13297/13153_fsm data/base/13297/13153_vm data/base/13297/13155 data/base/13297/13157 data/base/13297/13158 data/base/13297/13160 data/base/13297/13162 data/base/13297/1417 data/base/13297/1417_vm data/base/13297/1418 data/base/13297/1418_vm data/base/13297/174 data/base/13297/175 data/base/13297/2187 data/base/13297/2328 data/base/13297/2328_vm data/base/13297/2336 data/base/13297/2336_vm data/base/13297/2337 data/base/13297/2600 data/base/13297/2600_fsm data/base/13297/2600_vm data/base/13297/2601 data/base/13297/2601_fsm data/base/13297/2601_vm data/base/13297/2602 data/base/13297/2602_fsm data/base/13297/2602_vm data/base/13297/2603 data/base/13297/2603_fsm data/base/13297/2603_vm data/base/13297/2604 data/base/13297/2604_vm data/base/13297/2605 data/base/13297/2605_fsm data/base/13297/2605_vm data/base/13297/2606 data/base/13297/2606_fsm data/base/13297/2606_vm data/base/13297/2607 data/base/13297/2607_fsm data/base/13297/2607_vm data/base/13297/2608 data/base/13297/2608_fsm data/base/13297/2608_vm data/base/13297/2609 data/base/13297/2609_fsm data/base/13297/2609_vm data/base/13297/2610 data/base/13297/2610_fsm data/base/13297/2610_vm data/base/13297/2611 data/base/13297/2611_vm data/base/13297/2612 data/base/13297/2612_fsm data/base/13297/2612_vm data/base/13297/2613 data/base/13297/2613_vm data/base/13297/2615 data/base/13297/2615_fsm data/base/13297/2615_vm data/base/13297/2616 data/base/13297/2616_fsm data/base/13297/2616_vm data/base/13297/2617 data/base/13297/2617_fsm data/base/13297/2617_vm data/base/13297/2618 data/base/13297/2618_fsm data/base/13297/2618_vm data/base/13297/2619 data/base/13297/2619_fsm data/base/13297/2619_vm data/base/13297/2620 data/base/13297/2620_vm data/base/13297/2650 data/base/13297/2651 data/base/13297/2652 data/base/13297/2653 data/base/13297/2654 data/base/13297/2655 data/base/13297/2656 data/base/13297/2657 data/base/13297/2658 data/base/13297/2659 data/base/13297/2660 data/base/13297/2661 data/base/13297/2662 data/base/13297/2663 data/base/13297/2664 data/base/13297/2665 data/base/13297/2666 data/base/13297/2667 data/base/13297/2668 data/base/13297/2669 data/base/13297/2670 data/base/13297/2673 data/base/13297/2674 data/base/13297/2675 data/base/13297/2678 data/base/13297/2679 data/base/13297/2680 data/base/13297/2681 data/base/13297/2682 data/base/13297/2683 data/base/13297/2684 data/base/13297/2685 data/base/13297/2686 data/base/13297/2687 data/base/13297/2688 data/base/13297/2689 data/base/13297/2690 data/base/13297/2691 data/base/13297/2692 data/base/13297/2693 data/base/13297/2696 data/base/13297/2699 data/base/13297/2701 data/base/13297/2702 data/base/13297/2703 data/base/13297/2704 data/base/13297/2753 data/base/13297/2753_fsm data/base/13297/2753_vm data/base/13297/2754 data/base/13297/2755 data/base/13297/2756 data/base/13297/2757 data/base/13297/2830 data/base/13297/2830_vm data/base/13297/2831 data/base/13297/2832 data/base/13297/2832_vm data/base/13297/2833 data/base/13297/2834 data/base/13297/2834_vm data/base/13297/2835 data/base/13297/2836 data/base/13297/2836_vm data/base/13297/2837 data/base/13297/2838 data/base/13297/2838_fsm data/base/13297/2838_vm data/base/13297/2839 data/base/13297/2840 data/base/13297/2840_fsm data/base/13297/2840_vm data/base/13297/2841 data/base/13297/2995 data/base/13297/2995_vm data/base/13297/2996 data/base/13297/3079 data/base/13297/3079_fsm data/base/13297/3079_vm data/base/13297/3080 data/base/13297/3081 data/base/13297/3085 data/base/13297/3118 data/base/13297/3118_vm data/base/13297/3119 data/base/13297/3164 data/base/13297/3256 data/base/13297/3256_vm data/base/13297/3257 data/base/13297/3258 data/base/13297/3455 data/base/13297/3456 data/base/13297/3456_fsm data/base/13297/3456_vm data/base/13297/3466 data/base/13297/3466_vm data/base/13297/3467 data/base/13297/3468 data/base/13297/3501 data/base/13297/3501_vm data/base/13297/3502 data/base/13297/3503 data/base/13297/3534 data/base/13297/3541 data/base/13297/3541_fsm data/base/13297/3541_vm data/base/13297/3542 data/base/13297/3574 data/base/13297/3575 data/base/13297/3576 data/base/13297/3576_vm data/base/13297/3596 data/base/13297/3596_vm data/base/13297/3597 data/base/13297/3598 data/base/13297/3598_vm data/base/13297/3599 data/base/13297/3600 data/base/13297/3600_fsm data/base/13297/3600_vm data/base/13297/3601 data/base/13297/3601_fsm data/base/13297/3601_vm data/base/13297/3602 data/base/13297/3602_fsm data/base/13297/3602_vm data/base/13297/3603 data/base/13297/3603_fsm data/base/13297/3603_vm data/base/13297/3604 data/base/13297/3605 data/base/13297/3606 data/base/13297/3607 data/base/13297/3608 data/base/13297/3609 data/base/13297/3712 data/base/13297/3764 data/base/13297/3764_fsm data/base/13297/3764_vm data/base/13297/3766 data/base/13297/3767 data/base/13297/548 data/base/13297/549 data/base/13297/826 data/base/13297/826_vm data/base/13297/827 data/base/13297/828 data/base/13297/PG_VERSION data/base/13297/pg_filenode.map data/base/13297/pg_internal.init data/global/ data/global/1136 data/global/1136_fsm data/global/1136_vm data/global/1137 data/global/1213 data/global/1213_fsm data/global/1213_vm data/global/1214 data/global/1214_fsm data/global/1214_vm data/global/1232 data/global/1233 data/global/1260 data/global/1260_fsm data/global/1260_vm data/global/1261 data/global/1261_vm data/global/13293 data/global/13295 data/global/13296 data/global/2396 data/global/2396_fsm data/global/2396_vm data/global/2397 data/global/2676 data/global/2677 data/global/2694 data/global/2695 data/global/2697 data/global/2698 data/global/2846 data/global/2846_vm data/global/2847 data/global/2964 data/global/2964_vm data/global/2965 data/global/2966 data/global/2966_vm data/global/2967 data/global/3592 data/global/3592_vm data/global/3593 data/global/4060 data/global/4060_vm data/global/4061 data/global/6000 data/global/6000_vm data/global/6001 data/global/6002 data/global/pg_control data/global/pg_filenode.map data/global/pg_internal.init data/pg_clog/ data/pg_clog/0000 data/pg_commit_ts/ data/pg_dynshmem/ data/pg_log/ data/pg_log/postgresql-Thu.log data/pg_logical/ data/pg_logical/mappings/ data/pg_logical/snapshots/ data/pg_multixact/ data/pg_multixact/members/ data/pg_multixact/members/0000 data/pg_multixact/offsets/ data/pg_multixact/offsets/0000 data/pg_notify/ data/pg_notify/0000 data/pg_replslot/ data/pg_serial/ data/pg_snapshots/ data/pg_stat/ data/pg_stat/db_0.stat data/pg_stat/db_13297.stat data/pg_stat/global.stat data/pg_stat_tmp/ data/pg_subtrans/ data/pg_subtrans/0000 data/pg_tblspc/ data/pg_twophase/ data/pg_xlog/ data/pg_xlog/000000010000000000000002 data/pg_xlog/000000010000000000000003 data/pg_xlog/000000010000000000000004 data/pg_xlog/archive_status/ data/pg_xlog/archive_status/000000010000000000000002.done sent 73,002,481 bytes received 16,572 bytes 29,207,621.20 bytes/sec total size is 72,933,392 speedup is 1.00
Rename the old location :
mv /var/lib/pgsql/9.5/data /var/lib/pgsql/9.5/data_bkp
Pointing the database to the new location changing the parameter data_directory = from ” to ‘/postgres/data/’ in the file postgresql.conf located in the old directory .
# The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. data_directory = '/postgres/data/'
Restart the cluster in the new location:
-bash-4.2$ /usr/pgsql-9.5/bin/pg_ctl start -D /postgres/data server starting
Check the status of the cluster:
-bash-4.2$ /usr/pgsql-9.5/bin/pg_ctl status -D /postgres/data pg_ctl: server is running (PID: 30032) /usr/pgsql-9.5/bin/postgres "-D" "/postgres/data" -bash-4.2$
Check the value for data directory of the cluster:
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
-bash-4.2$ psql postgres
psql (9.5.21)
Type "help" for help.
postgres=# SHOW data_directory;
data_directory
----------------
/postgres/data
(1 row)
postgres=#
Remove the old location:
rm -Rf /var/lib/pgsql/9.5/data_bkp
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)