Upgrading to PostgreSQL 11 on Centos 7

Since my previous article Upgrading to PostgreSQL 10 on Centos 7 was so popular, I though I’d do a follow-up for anyone looking to upgrade a very simply configured PostgreSQL 10 server to PostgreSQL 11 on Centos 7.

First, and this goes without saying, backup your server!

Install the repo RPM for PosgresSQL 10
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Install PosgreSQL 11
sudo yum install postgresql11-server
Extensions

If you’re using extensions like pg_crypto, you will also need the postgresql11-contrib package

sudo yum install postgresql11-contrib
Stop Postgresql 10 and Postgresql 11
sudo systemctl stop postgresql-10.service && sudo systemctl stop postgresql-11.service
Initialize the PostgreSQL 11 database
sudo su postgres
cd ~/
/usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data/
Migrate your database from the 10.x version to 11.x
/usr/pgsql-11/bin/pg_upgrade --old-datadir /var/lib/pgsql/10/data/ --new-datadir /var/lib/pgsql/11/data/ --old-bindir /usr/pgsql-10/bin/ --new-bindir /usr/pgsql-11/bin/
Edit configuration files

Make any necessary changes to postgresql.conf . Iā€™d recommend making the changes to the new version rather than copying over postgresql.conf from 10.

You can view your 10 configuration with:

nano /var/lib/pgsql/10/data/postgresql.conf

You can make your changes to the 11 configuration with:

nano /var/lib/pgsql/11/data/postgresql.conf

If you need to connect from other servers, make sure to change:

#listen_addresses = 'localhost'

to (apostrophes may not survive copy/paste, may want to hand enter)

listen_addresses = '*'

Now do the same with pg_hba.conf

View the old configuration

nano /var/lib/pgsql/10/data/pg_hba.conf

Edit the new configuration

nano /var/lib/pgsql/11/data/pg_hba.conf
Start the server
systemctl start postgresql-11.service
Analyze and optimize the new cluster
./analyze_new_cluster.sh
Enable the PostgreSQL 11 Service (to start automatically)
systemctl enable postgresql-11
Remove PostgreSQL 10 and its data (if so desired)
./delete_old_cluster.sh
exit
sudo yum remove postgresql10-server

That should do it!

ZFS on Ubuntu server

Ubuntu Home Server Setup Part II

Welcome to Part II of my Ubuntu Home Server build! In Part I, I did a very basic Ubuntu Server install. In this part, I’ll be creating a ZFS pool and volumes to store all my data on.

Other parts of this guide can be found at:

Home Server With Ubuntu

Setup

I’ll be setting up a server with 8 physical drives.

Disk 0: SSD for OS

Disk 1: SSD for ZFS Intent Log (improves write performance)
(read fantastic information about it here: http://nex7.blogspot.com/2013/04/zfs-intent-log.html)

Disk 2: SSD for L2ARC caching (improves read performance)

Disk 3 – 7: HDDs for ZFS Pool (where all my data with be stored)

Quick disclosure: I’m *far* from a ZFS expert. From what I’ve gleaned, this should suffice for home / small business use. If you’re planning something enterprise-grade, find an expert!

Install Ubuntu

Perform a regular Ubuntu server installation, or use an existing server.

SSH Into the server, rather than using the console. You’ll want to be able to copy and paste when you setup the zpool.

Install ZFS

sudo apt install zfsutils-linux

Create the ZPOOL

I’ll be using RAIDZ (which is like RAID-5) to get redundancy on my disks without losing too much usable space.

ZFS offers many other options, like RAID0, 1, 6, etc. Use whichever is appropriate for your workload.

It is very strongly recommended to not use disk names like sdb, sdc, etc. Those might change across reboots.

Many of the articles I’ve read suggest using UUIDs . However, my experience on Ubuntu Server is that these are not assigned to blank disks. Therefore, I will be using disk paths instead.

These are verbose and a bit of a pain to type, but they make sure you know exactly what disk you are referring to should you need to swap drives in the future. They will also not change on reboots.

To see your installed disks run:

ls -lh /dev/disk/by-path

My output looks like

adam@normandy:~$ ls -lh /dev/disk/by-path
 total 0
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:00:1f.2-ata-5 -> ../../sr0
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:0:0 -> ../../sda
 lrwxrwxrwx 1 root root 10 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:0:0-part1 -> ../../sda1
 lrwxrwxrwx 1 root root 10 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:0:0-part2 -> ../../sda2
 lrwxrwxrwx 1 root root 10 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:0:0-part3 -> ../../sda3
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:1:0 -> ../../sdb
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:2:0 -> ../../sdc
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:3:0 -> ../../sdd
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:4:0 -> ../../sde
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:5:0 -> ../../sdf
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:6:0 -> ../../sdg
 lrwxrwxrwx 1 root root  9 Jul  8 09:06 pci-0000:02:00.0-scsi-0:0:7:0 -> ../../sdh

I chose to install Linux on my 1st drive (sda). I’ll be using sdb for the ZIL, sdc for L2ARC, and sdd, sde, sdf, sdg, and sdh to for the data pool.

First, I’ll setup the data pool. This is where SSH is handy, since you can copy/paste your paths from above.

In my example below, I’m naming my pool “data.” You can use a different name if you’d like. If your setup is like mine, you’ll create one pool with many volumes in it.

I’m using drives with 4k physical sectors, so I’m adding the option: -o ashift=12
This should increase performance, but at the cost of total storage space. You an remove this option if you don’t think it’s a good fit for you.

sudo zpool create data -o ashift=12 raidz /dev/disk/by-path/pci-0000:02:00.0-scsi-0:0:3:0 /dev/disk/by-path/pci-0000:02:00.0-scsi-0:0:4:0 /dev/disk/by-path/pci-0000:02:00.0-scsi-0:0:5:0 /dev/disk/by-path/pci-0000:02:00.0-scsi-0:0:6:0 /dev/disk/by-path/pci-0000:02:00.0-scsi-0:0:7:0

To confirm this worked, run:

zpool list

You should have something like:

adam@normandy:~$ zpool list
NAME   SIZE  ALLOC   FREE  EXPANDSZ   FRAG    CAP  DEDUP  HEALTH  ALTROOT
data  18.1T   238K  18.1T         -     0%     0%  1.00x  ONLINE  -

Next I’ll tell ZFS to use sdb as the ZFS Intent Log

sudo zpool add data log /dev/disk/by-path/pci-0000:02:00.0-scsi-0:0:1:0

Then I’ll tell ZFS to use sdc as the L2ARCH cache

sudo zpool add data cache /dev/disk/by-path/pci-0000:02:00.0-scsi-0:0:2:0

If I run zpool status, I should see my data, ZIL, and cache drives

adam@normandy:/data/download/secure$ zpool status
  pool: data
 state: ONLINE
  scan: none requested
config:

        NAME                               STATE     READ WRITE CKSUM
        data                               ONLINE       0     0     0
          raidz1-0                         ONLINE       0     0     0
            pci-0000:02:00.0-scsi-0:0:3:0  ONLINE       0     0     0
            pci-0000:02:00.0-scsi-0:0:4:0  ONLINE       0     0     0
            pci-0000:02:00.0-scsi-0:0:5:0  ONLINE       0     0     0
            pci-0000:02:00.0-scsi-0:0:6:0  ONLINE       0     0     0
            pci-0000:02:00.0-scsi-0:0:7:0  ONLINE       0     0     0
        logs
          pci-0000:02:00.0-scsi-0:0:1:0    ONLINE       0     0     0
        cache
          pci-0000:02:00.0-scsi-0:0:2:0    ONLINE       0     0     0

errors: No known data errors

Create the Filesystem

Now that the zpool exists, we can create filesystems on top of it.
A pool can have multiple filesystems. I’ll create one for media, and one for virtual machines (because that’s what I need).

sudo zfs create data/media
sudo zfs create data/vm

To confirm it was created correctly run:

zfs list

And it should look something like this:

adam@normandy:~$ zfs list
 NAME         USED  AVAIL  REFER  MOUNTPOINT
 data         210K  14.0T  36.7K  /data
 data/media  35.1K  14.0T  35.1K  /data/media
 data/vm     35.1K  14.0T  35.1K  /data/vm

All of your zfs filesystems are automatically mounted.

adam@normandy:~$ mount
...
data on /data type zfs (rw,xattr,noacl)
data/media on /data/media type zfs (rw,xattr,noacl)
data/vm on /data/vm type zfs (rw,xattr,noacl

You can use them just as you would any mounted filesystem. That’s it!

Basic Ubuntu Server Installation

This is Part I (the boring part) of my Ubuntu Home Server install.

Other parts can be found at:

Home Server With Ubuntu

For anyone who’s installed Ubuntu Server before, there’s not much here for you. I’m putting this here for anyone starting out with Ubuntu and for the sake of completeness.

Also, my 1st warning: this is the setup I think will serve me best for my particular situation. It may not be the best for you, and, while it’s somewhat redundant, it certainly isn’t “enterprise-grade.” You were warned šŸ˜‰

In the steps below, anytime you see something in brackets, replace it with the correct value for your system, without the brackets.
For example, if you see:
ssh [username]@[ip address]
You should really enter something like:
ssh me@192.168.1.1

Why Ubuntu?

All the major Linux distros are awesome. You really can’t go wrong! For servers, I’ve typically gone with Centos in the past (and on this Ubuntu server will be many Centos virtual machines). However, there is one reason I’ve decided to go with Ubuntu in this instance: ZFS. Ubuntu has ZFS baked in, whereas Centos and Fedora require recompilation of kernel modules after major OS upgrades. Since I want this box to be as turnkey as possible (if it goes down, my internet will go down as well), Ubuntu it is!

Installation

First, download the Ubuntu Server iso from Ubuntu. I’ll be using the 18.04 LTS release, since I prefer to stick to LTS releases for critical infrastructure.

https://ubuntu.com/download/server

Next, either burn the iso to a DVD or image it to a flash drive. If you use the flash drive method, I recommend Fedora Media Writer. It’s available for Windows, MacOS, and Linux, and will image pretty much any Linux distro to USB.

https://getfedora.org/en/workstation/download/

Once you’ve got a bootable DVD or flash drive, boot from it. Most servers and workstations will tell you which key to press on the keyboard to get to your BIOS/UEFI boot menu.

After booting, choose *Install Ubuntu Server.

Choose your language.

Choose your keyboard layout.

Choose Install Ubuntu.

I’m going to use DHCP for now and set static IP later when I configure the virtualization networks for KVM. If you need to configure a static IP, you can do so here.

If you use an internet proxy, set it here.

Choose the default Ubuntu mirror.

I prefer to use LVM in case I need to resize partitions in the future.

I’ll be using one SSD as a boot volume. Choose whichever drive you’ll be booting from. I’ll be using all of the rest of the drives for ZFS, so I’ll leave them as they are for now.

By default, Ubuntu will only use 4GB of your drive for the root partition. Since all of my other data will live my ZFS volumes, I’ll expand the volume to use the whole 1TB.

To change the size of the root volume, use the down arrow to chose “ubuntu-lv,” press Enter, then choose “Edit.”

Ubuntu will helpfully tell you the max size you can set the partition to. Enter that number and choose “Save.”

Choose “Done.”

Let Ubuntu know your name, your computer’s name, the username you’d like to use, and the password you’d like to use.

You now have the option of installing a secure shell server. This will allow you to log in remotely. I’ll be installing this.

You also have the option of installing some other services. You can always install these later. I’ll be skipping them and just choosing “Done.”

When the installation has finished, choose “Reboot Now.”

Remove the bootable DVD or flash drive and press Enter.

Log In

Once the server has rebooted, you can log in to the server itself or via SSH (if you installed SSH).

If you need to find out your server’s IP address for SSH, log in via the console and run the following:

ip address

Then on the computer you are using to SSH into the server run:

ssh [username]@[ip address]

Updates

Before anything else, let’s make sure everything is up-to-date.

sudo apt upgrade

Once that has completed, you may need to reboot.

sudo reboot

KDE On a Server?

Let’s get right to it: it’s not considered security-wise to install a GUI on a server. However, I’ll be using things like Handbrake and Virtual Machine Manager, so I’ll be putting on KDE. To add a bit of security and save memory, I’ll manually start KDE when I need it.

To install just the very minimum of KDE (you can always add the other bits later), run:

sudo apt install kubuntu-desktop --no-install-recommends

I’m also going to install a couple other KDE apps to make my life easier. KDE’s Konsole terminal and the dolphin file manager:

sudo apt install konsole dolphin

If you want all of KDE, and have it start be default, you can simply run this instead:

sudo apt install kubuntu-desktop

If GNOME is more your thing, you can install it with:

sudo apt install ubuntu-gnome-desktop

If you install just the minimum KDE, your server will still boot in console mode. To start KDE, simply log in and run:

startx

Since I’ll often want to use the UI remotely, I’m also going to install a package called xrdp. This will serve a desktop over the RDP protocol so I can get a desktop remotely:

sudo apt install xrdp

This will install xrdp, configure the service to start automatically, and start the service. Once it’s finished, you can connect to your server’s IP address via any remote desktop app and use the same username and password you use to log in locally.

Home Server With Ubuntu

I finally picked up a used Dell PowerEdge R720 from the fine folks at ServerMoney to replace my current home server (a Frankenstein of workstation parts).

I thought I’d document my setup for anyone that might be interested and for my future self that wondered what exactly I did in the 1st place šŸ˜œ

My server needs are quite diverse, so I’ll break this guide into separate posts for each one to keep things organized. (links will be active once each part is finished)

Happy serving!

Part I: Basic Ubuntu Server Install (SSH, KDE, & xrdp)
Part II: Ubuntu ZFS Setup
Part III: Ubuntu Virtualization Server with KVM
Part IV: pfSense on KVM
Part V: Plex on Ubuntu
Part VI: SMB & NFS

Audio Fix for Mass Effect on Steam for Linux

I’m a few days late for N7 day, but I figure this information is useful nonetheless!

I’ve always done my Mass Effecting on XBox 360 or XBox One. But now that Steam has many Windows games working on Linux, I figured: what heck, let’s start over again there! (I’m not the only one who plays Mass Effect on loop right? Bueller? Bueller?)

Everything worked like magic right from the get-go, except audio. You’ll probably get sound from the corporate logos, but nothing when you play the game. If you try to turn off hardware audio in the settings, which is culprit, Mass Effect will dutifully turn it back on again.

So, by way of the Arch Linux forums, here’s the fix:

Open up a terminal window.

Make a copy of the existing config, just in case:

cp ~/.steam/steam/steamapps/common/Mass\ Effect/Engine/Config/BaseEngine.ini ~/Desktop/

Edit the configuration file:

gedit ~/.steam/steam/steamapps/common/Mass\ Effect/Engine/Config/BaseEngine.ini

Scroll down to the section with the heading: [ISACTAudio.ISACTAudioDevice]

Copy and paste these two lines right below the heading:

DeviceName=Generic Software
UseEffectsProcessing=False

And that’s it! Fire up Mass Effect, and you should get audio.

Rescuing Fedora When EFI Grub Goes Bad

On a very ordinary day I decided to upgrade my Plex server from Fedora Server 27 to version 29. When I rebooted, Grub failed to find anything bootable. Then my day stopped being ordinary.

Luckily I snapshotted the VM first, since figuring this out involved restoring and trying again more times than I’d like to admit. But enough of my story of woe. If you’re here, you just want to know how to fix grub!

The relevant information is in Fedora’s official documentation, but I’ll give you the quick version of what worked for me: https://fedoraproject.org/wiki/GRUB_2?rd=Grub2#Updating_GRUB_2_configuration_on_UEFI_systems

Likely you rebooted before noticing something was wrong. If this was a VM that you have a working snapshot of, restore the snapshot and skip ahead to “Fixin’ Time!”. If not, boot a Fedora live DVD, choose rescue, then option 1 to mount all of your partitions.

Once mounted, follow the on-screen instructions to chroot into your mounted partition.

Fixin’ Time!

Make sure /boot and /boot/efi are mounted. If not, this fix won’t work.

sudo -s

mount /boot && mount /boot/efi

If you’ve got a standard installation like mine, you won’t have the grub tools installed, so install them.

dnf install grub2-efi-x64 shim-x64

Now the magic step.

grub2-mkconfig -o /boot/efi/EFI/fedora/grub.cfg

Important Note!

Do NOT run grub2-install. This is not for EFI systems and will end up with getting nothing but a blank Grub prompt.

Finally, reboot.

reboot

That’s it! You should have a working Grub menu now. If not, you may need to try some of the additional steps listed in Fedora’s documentation linked above.

SSLStream Failure with .Net 4.x in Unity 2018.1

When Unity 2018.1 was released I jumped (perhaps too quickly) to using “.Net 4.x Equivalent” for the scripting runtime version under “Build Settings->Player Settings.”

Everything ran fine in the Unity Editor, but socket reads would silently fail after a few seconds on iOS.

I use SSLStream and SSLStream.Read() in a while loop running under its own thread. This runs without any issue using IL2CPP under the .Net 3.5 runtime, but fails almost immediately under iOS using 4.x.

I’m not sure what the exact problem is, but a forum post on unity.com seems to indicate that TLS related functions aren’t going to be fully ready using the .Net 4.x runtime and IL2CPP until 2018.2:
https://forum.unity.com/threads/questions-around-tls-support-on-2018-1.524917/

Either way, if you come across this issue, simply reverting back to .Net 3.5 seems to resolve the problem.

Upgrading to PostgreSQL 10 on Centos 7

(An updated version of this post for upgrading to PostgreSQL 11 is available here)

Here’s a quick rundown on upgrading a very simply configured PostgreSQL 9.x server to PostgreSQL 10 running on Centos 7.

First, and this goes without saying, backup your server!

In these examples, I’m using upgrading from PostgreSQL 9.5. If you’re upgrading from a different version, just replace 9.5 and 95 wherever you see it with your appropriate version number.

Install the repo RPM for PosgresSQL 10

sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm

Install PosgreSQL 10

sudo yum install postgresql10-server

Stop Postgresql 9.5 and Postgresql 10

sudo systemctl stop postgresql-9.5.service && sudo systemctl stop postgresql-10.service

Initialize the PostgreSQL 10 database

sudo su postgres
cd ~/
/usr/pgsql-10/bin/initdb -D /var/lib/pgsql/10/data/

Migrate your database from the 9.x version to 10

/usr/pgsql-10/bin/pg_upgrade --old-datadir /var/lib/pgsql/9.5/data/ --new-datadir /var/lib/pgsql/10/data/ --old-bindir /usr/pgsql-9.5/bin/ --new-bindir /usr/pgsql-10/bin/

Make any necessary changes to postgresql.conf . I’d recommend making the changes to the new version rather than copying over postgresql.conf from 9.5, since there are a bunch of new options in the PostreSQL 10 version of the file.

You can view your 9.5 configuration with:

nano /var/lib/pgsql/9.5/data/postgresql.conf

You can make your changes to the 10 configuration with:

nano /var/lib/pgsql/10/data/postgresql.conf

If you need to connect from other servers, make sure to change:

#listen_addresses = 'localhost'

to (apostrophes may not survive copy/paste, may want to hand enter)

listen_addresses = '*'

(or whatever is appropriate for you)

Now do the same with pg_hba.conf

View the old configuration

nano /var/lib/pgsql/9.5/data/pg_hba.conf

Edit the new configuration

nano /var/lib/pgsql/10/data/pg_hba.conf

Start the server

systemctl start postgresql-10.service

Analyze and optimize the new cluster

./analyze_new_cluster.sh

If everything is working, set the PostgreSQL 10 service to start automatically

systemctl enable postgresql-10

If you wish to remove PostgreSQL 9.x and its data

./delete_old_cluster.sh
exit
sudo yum remove postgresql95-server
sudo yum remove pgdg-centos95

That should do it!

Fixing 404 Errors on WordPress with Let’s Encrypt

Since my SSL cert was nearing expiration, I thought it would be a good idea to give Let’s Encrypt (free SSL certs!) a try.

Let’s Encrypt has a helper app called certbot that will configure Apache for you automatically. The really nice thing about certbot is that it will also (via crontab) renew your cert and configure Apache to use the new cert. This is useful, since Let’s Encrypt certs expire every 90 days.

To use certbot effectively, you need an Apache configuration that’s setup the way your distro expects. Mine was not (I hand ported the configs from Ubuntu), so I figured it was a good time to reinstall Apache with the default configs, then run certbot (official instructions here: https://certbot.eff.org/ ).

This initially seemed to work great, but I quickly noticed all of my subpages returned 404 errors. WordPress works best when you allow it to configure a .htaccess file to do URL rewrites. Allowing URL rewrites via .htaccess requires some additional configuration in your ssl.conf file.

sudo nano /etc/httpd/conf.d/ssl.conf

Add the following just before </VirtualHost> at the very end of your config.

<Directory /var/www/html/>
Options FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
</Directory>

Thanks to Mike McMurray who posted the instructions at: https://mike.mcmurray.co.nz/2017/01/08/wordpress-permalink-404-with-https/

 

Fix: Office Updates and Malwarebytes

If any of you are big fans of Malwarebytes (and why wouldn’t you be?) you may be experiencing crashes in Office 2013 and 2016 under Windows 10.

Microsoft has identified the problem: https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Word-for-Windows-bf6bf17c-2807-4871-83ce-e337ae8f0b86?ui=en-US&rs=en-US&ad=US

The workaround is to use the latest beta of Malware Bytes: https://forums.malwarebytes.com/topic/200230-malwarebytes-version-310-beta-available-for-download/

Hope this helps!