Select Page

Advent

Databases develop over the years, infrequently outgrowing the gap on their unique document device. Once they’re positioned at the similar partition as the remainder of the working device, this will additionally probably result in I/O rivalry.

RAID, community block garage, and different gadgets can be offering redundancy and fortify scalability, along side different fascinating options. Whether or not you’re including extra space, comparing tactics to optimize efficiency, or taking a look to make the most of different garage options, this instructional will information you thru relocating PostgreSQL’s records listing.

Necessities

To finish this information, you’ll want:

On this instance, we’re transferring the information to a block garage instrument fastened at /mnt/volume_nyc1_01. If you’re the use of Block Garage on DigitalOcean, this guide allow you to mount your quantity ahead of proceeding with this instructional.

Irrespective of what underlying garage you utilize, regardless that, the next steps allow you to transfer the information listing to a brand new location.

Step 1 — Transferring the PostgreSQL Information Listing

Earlier than we get began with transferring PostgreSQL’s records listing, let’s test the present location via beginning an interactive PostgreSQL consultation. Within the following command, psql is the command to go into the interactive track and -u postgres tells sudo to execute psql because the device’s postgres consumer:

Upon getting the PostgreSQL steered spread out, use the next command to turn the present records listing:

Output

data_directory ------------------------------ /var/lib/postgresql/10/primary (1 row)

This output confirms that PostgreSQL is configured to make use of the default records listing, /var/lib/postgresql/10/primary, in order that’s the listing we want to transfer. As soon as you might have showed the listing in your device, sort q and press ENTER to near the PostgreSQL steered.

To make sure the integrity of the information, forestall PostgreSQL ahead of you in truth make adjustments to the information listing:

  • sudo systemctl forestall postgresql

systemctl does not show the result of all carrier control instructions. To ensure that you simply’ve effectively stopped the carrier, use the next command:

  • sudo systemctl standing postgresql

The general line of the output must let you know that PostgreSQL has been stopped:

Output

. . . Jul 12 15:22:44 ubuntu-512mb-nyc1-01 systemd[1]: Stopped PostgreSQL RDBMS.

Now that the PostgreSQL server is close down, we’ll reproduction the prevailing database listing to the brand new location with rsync. The usage of the -a flag preserves the permissions and different listing houses, whilst -v supplies verbose output so you’ll observe the growth. We’re going to begin the rsync from the postgresql listing in an effort to mimic the unique listing construction within the new location. By way of developing that postgresql listing throughout the mount-point listing and preserving possession via the PostgreSQL consumer, we will keep away from permissions issues for long term upgrades.

Be aware: Ensure there’s no trailing slash at the listing, that may be added when you use tab of completion. Should you do come with a trailing slash, rsync will unload the contents of the listing into the mount level as an alternative of copying over the listing itself.

The model listing, 10, isn’t strictly vital since we’ve outlined the site explicitly within the postgresql.conf document, however following the challenge conference no doubt received’t harm, particularly if there’s a necessity one day to run more than one variations of PostgreSQL:

  • sudo rsync -av /var/lib/postgresql /mnt/volume_nyc1_01

As soon as the reproduction is entire, we will rename the present folder with a .bak extension and stay it till we’ve showed that the transfer was once a hit. This may assist to keep away from confusion that would rise up from having similarly-named directories in each the brand new and the outdated location:

  • sudo mv /var/lib/postgresql/10/primary /var/lib/postgresql/10/primary.bak

Now we’re able to configure PostgreSQL to get entry to the information listing in its new location.

Step 2 — Pointing to the New Information Location

By way of default, the data_directory is ready to /var/lib/postgresql/10/primary within the /and many others/postgresql/10/primary/postgresql.conf document. Edit this document to replicate the brand new records listing:

  • sudo nano /and many others/postgresql/10/primary/postgresql.conf

In finding the road that starts with data_directory and alter the trail which follows to replicate the brand new location. Within the context of this instructional, the up to date directive will seem like this:

/and many others/postgresql/10/primary/postgresql.conf

. . .
data_directory = '/mnt/volume_nyc1_01/postgresql/10/primary'
. . .

Save and shut the document via urgent CTRL + X, Y, then ENTER. That is all you want to do to configure PostgreSQL to make use of the brand new records listing location. All that’s left at this level is to begin the PostgreSQL carrier once more and test that it’s certainly pointing to the proper records listing.

Step 3 — Restarting PostgreSQL

After converting the data-directory directive within the postgresql.conf document, pass forward and get started the PostgreSQL server the use of systemctl:

  • sudo systemctl get started postgresql

To substantiate that the PostgreSQL server began effectively, test its standing via once more the use of systemctl:

  • sudo systemctl standing postgresql

If the carrier began as it should be, you’ll see the next line on the finish of this command’s output:

Output

. . . Jul 12 15:45:01 ubuntu-512mb-nyc1-01[1]: Began PostgreSQL RDBMS. . . .

Finally, to be sure that the brand new records listing is certainly in use, open the PostgreSQL command steered.

Take a look at the worth for the information listing once more:

Output

data_directory ----------------------------------------- /mnt/volume_nyc1_01/postgresql/10/primary (1 row)

This confirms that PostgreSQL is the use of the brand new records listing location. Following this, take a second to make certain that you’re in a position to get entry to your database in addition to have interaction with the information inside. When you’ve verified the integrity of any current records, you’ll take away the backup records listing:

  • sudo rm -Rf /var/lib/postgresql/10/primary.bak

With that, you might have effectively moved your PostgreSQL records listing to a brand new location.

Conclusion:

Should you’ve adopted alongside, your database must be working with its records listing within the new location and also you’ve finished a very powerful step towards with the ability to scale your garage. You may additionally need to try 5 Common Server Setups For Your Web Application for concepts on methods to create a server infrastructure that will help you scale and optimize internet programs.