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, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)