HTTPSConnectionPool(host=’files.pythonhosted.org’, port=443): Read timed out

June 22, 2023June 22, 2023 adminLeave a comment

I recently had an issue where one of our EMR clusters failed to bootstrap the python modules via PIP. I checked the logs and saw that we ran into the following error:

HTTPSConnectionPool(host='files.pythonhosted.org', port=443): Read timed out

1	HTTPSConnectionPool(host='files.pythonhosted.org', port=443): Read timed out

I wanted to have PIP not die if it timed out, I also wanted it to retry on failure. By adding the following to my bootstrap.sh I was able to have the PIP socket timeout at a longer interval, also bump up the retries to 10. I have not seen the issue since I applied the new settings.

sudo python3 -m pip --timeout 100 --retries 10 install --upgrade pip
sudo python3 -m pip --timeout 100 --retries 10 install

1 2	sudo python3 -m pip --timeout 100 --retries 10 install --upgrade pip sudo python3 -m pip --timeout 100 --retries 10 install

From the PIP help page:

  --retries <retries>         Maximum number of retries each connection should attempt (default 5 times).
  --timeout <sec>             Set the socket timeout (default 15 seconds).

1 2	--retries <retries> Maximum number of retries each connection should attempt (default 5 times). --timeout <sec> Set the socket timeout (default 15 seconds).

Capture AWS CLI Output With Timestamps On Each Line Of Output

December 31, 2020 adminLeave a comment

I needed a way to get output from aws cli captured into a log file with timestamps, out of the box the aws cli output has no timestamps in the output. If you execute a aws s3 cp command, something like this:

aws s3 cp s3://jason-test-bucket-1/test_part_00 s3://jason-test-bucket-2/jason_test/

1	aws s3 cp s3://jason-test-bucket-1/test_part_00 s3://jason-test-bucket-2/jason_test/

You will see output like so:

copy: s3://jason-test-bucket-1/test_part_00 to s3://jason-test-bucket-2/jason_test/test_part_00

1	copy: s3://jason-test-bucket-1/test_part_00 to s3://jason-test-bucket-2/jason_test/test_part_00

As you can see this does not show a timestamp in each event of output from the aws cli. So I scoured the internet and found out some interesting things. Turns out that aws cli out of the box outputs with carriage returns instead of newlines. So trying standard awk piping methods was not working. Also aws cli has the ability to change the output, so I needed to add a cli parameter to set output to text. Next we needed to use TR to substitute the carriage returns with newlines, finally we can pipe to awk and print a timestamp on each output event from the aws cli. The final command and output looks like this:

#!/bin/bash
log='test.log'
aws s3 --output text cp s3://jason-test-bucket-1/test_part_00 s3://jason-test-bucket-2/jason_test/ | tr "\r" "\n" > >(awk '{print strftime("%Y-%m-%d:%H:%M:%S ") $0}') | tee >> $log 2>&1

#!/bin/bash

log='test.log'

aws s3 --output text cp s3://jason-test-bucket-1/test_part_00 s3://jason-test-bucket-2/jason_test/ | tr "\r" "\n" > >(awk '{print strftime("%Y-%m-%d:%H:%M:%S ") $0}') | tee >> $log 2>&1

Produces the following in the log file which is my desired result:

2020-12-31:13:32:13 Completed 726.3 KiB/726.3 KiB (3.8 MiB/s) with 1 file(s) remaining
2020-12-31:13:32:13 copy: s3://jason-test-bucket-1/test_part_00 to s3://jason-test-bucket-2/jason_test/test_part_00

1 2	2020-12-31:13:32:13 Completed 726.3 KiB/726.3 KiB (3.8 MiB/s) with 1 file(s) remaining 2020-12-31:13:32:13 copy: s3://jason-test-bucket-1/test_part_00 to s3://jason-test-bucket-2/jason_test/test_part_00

I hope this helps someone else as it was a bear to solve for me.

AWS CLI Max Concurrent Requests Tuning

January 3, 2020November 11, 2021 admin4 Comments

In this post I would like to go over how I tuned a test server for copying / syncing files from the local filesystem to S3 over the internet. If you ever had the task of doing this, you will notice that as the file count grows, so does the time it takes to upload the files to S3. After some web searching I found out that AWS allows you to tune the config to allow more concurrency than default.
AWS CLI S3 Config

The parameter that we will be playing with is max_concurrent_requests
This has a default value of 10, which allows only 10 requests to the AWS API for S3. Lets see if we can make some changes to that value and get some performance gains. My test setup is as follows:

2 x Intel(R) Xeon(R) CPU E5-2699 v3 @ 2.30GHz
8GB RAM
CentOS release 6.10 (Final)

2 x Intel(R) Xeon(R) CPU E5-2699 v3 @ 2.30GHz

8GB RAM

CentOS release 6.10 (Final)

I have 56 102MB files in the test directory:

-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_7.csv.gz
-rw-r--r-- 1 jasonr domain^users 102M Sep 24 11:44 sample__0_0_53.csv.gz
-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_6.csv.gz
-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_8.csv.gz
-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_55.csv.gz
--snip--
[jasonr@jr-sandbox jason_test]$ ls| wc -l
56

-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_7.csv.gz

-rw-r--r-- 1 jasonr domain^users 102M Sep 24 11:44 sample__0_0_53.csv.gz

-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_6.csv.gz

-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_8.csv.gz

-rw-r--r-- 1 jasonr domain^users 101M Sep 24 11:44 sample__0_0_55.csv.gz

--snip--

[jasonr@jr-sandbox jason_test]$ ls| wc -l

For the first test I am going to run aws s3 sync with no changes, so out of the box it should have 10 max_concurrent_requests. Lets use the Linux time command to gather the time result to copy all 56 files to S3. I will delete the folder on S3 with each iteration to keep the test the same. You can also view the 443 requests via netstat and count them as well to show whats going on. In all the tests my best result was 250. So as you can see you will need to play with the settings to get the best result, these settings will change along with the server specs.

1. 1m25.919s with the default configuration:

[jasonr@jr-sandbox jason_test]$ time aws s3 sync . s3://dev-redshift/jason_sync_test/
upload: ./sample__0_0_0.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_0.csv.gz
upload: ./sample__0_0_10.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_10.csv.gz
upload: ./sample__0_0_11.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_11.csv.gz
upload: ./sample__0_0_12.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_12.csv.gz
upload: ./sample__0_0_13.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_13.csv.gz
--snip--

real	1m25.919s
user	0m35.153s
sys	0m15.879s

[jasonr@jr-sandbox jason_test]$ time aws s3 sync . s3://dev-redshift/jason_sync_test/

upload: ./sample__0_0_0.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_0.csv.gz

upload: ./sample__0_0_10.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_10.csv.gz

upload: ./sample__0_0_11.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_11.csv.gz

upload: ./sample__0_0_12.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_12.csv.gz

upload: ./sample__0_0_13.csv.gz to s3://dev-redshift/jason_sync_test/sample__0_0_13.csv.gz

--snip--

real 1m25.919s

user 0m35.153s

sys 0m15.879s

2. Now lets set the max conqurent requests to 20 and try again, you can do this with the command below, after running we can see a little gain.

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 20
[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config 
[default]
s3 =
    max_concurrent_requests = 20
[root@jr-sandbox ~]# netstat -an| grep 443| wc -l
20

real	1m13.277s
user	0m36.186s
sys	0m16.462s

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 20

[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config

[default]

s3 =

max_concurrent_requests = 20

[root@jr-sandbox ~]# netstat -an| grep 443| wc -l

real 1m13.277s

user 0m36.186s

sys 0m16.462s

3. Bumped up to 50 shows a bit more gain:

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 50
[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config 
[default]
s3 =
    max_concurrent_requests = 50

[root@jr-sandbox ~]# netstat -an| grep 443| wc -l
49
real	1m0.720s
user	0m37.669s
sys	0m19.344s

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 50

[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config

[default]

s3 =

max_concurrent_requests = 50

[root@jr-sandbox ~]# netstat -an| grep 443| wc -l

real 1m0.720s

user 0m37.669s

sys 0m19.344s

4. Bumped up to 100, I start to notice that we lost some speed:

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 100
[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config 
[default]
s3 =
    max_concurrent_requests = 100
[root@jr-sandbox ~]# netstat -an| grep 443| wc -l
95
real	1m4.212s
user	0m39.737s
sys	0m21.847s

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 100

[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config

[default]

s3 =

max_concurrent_requests = 100

[root@jr-sandbox ~]# netstat -an| grep 443| wc -l

real 1m4.212s

user 0m39.737s

sys 0m21.847s

5. Bumped up to 250 we see the best result so far:

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 250
[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config 
[default]
s3 =
    max_concurrent_requests = 250
[root@jr-sandbox ~]# netstat -an| grep 443| wc -l
234
real	0m55.036s
user	0m42.841s
sys	0m21.409s

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 250

[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config

[default]

s3 =

max_concurrent_requests = 250

[root@jr-sandbox ~]# netstat -an| grep 443| wc -l

234

real 0m55.036s

user 0m42.841s

sys 0m21.409s

6. Bumped up to 500, we lose performance, most likely due to the machine resources.

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 500
[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config 
[default]
s3 =
    max_concurrent_requests = 500
[root@jr-sandbox ~]# netstat -an| grep 443| wc -l
465
real	1m16.593s
user	0m50.336s
sys	0m25.806s

[jasonr@jr-sandbox jason_test]$ aws configure set default.s3.max_concurrent_requests 500

[jasonr@jr-sandbox jason_test]$ cat ~/.aws/config

[default]

s3 =

max_concurrent_requests = 500

[root@jr-sandbox ~]# netstat -an| grep 443| wc -l

465

real 1m16.593s

user 0m50.336s

sys 0m25.806s

So to wrap up, you can tune the amount of concurrent requests allowed from the aws cli to s3, you will need to play with this setting to get the best results for your machine.

Mass Rename Files In Gcloud With Python Multiprocessing Parallel Gsutil

September 24, 2019January 4, 2020 admin2 Comments

I had been tasked with renaming in place, up in the cloud, not bringing the files down locally, 50000 files. I looked at using wildcards with gsutil however I was not able to remove what I wanted from the file, so I set out on creating a shell script to perform the task, I created a listing of files with gsutil and did some awk magic to get just the filenames into listing2.txt. I wrote the following loop.

This will rename the files stripping out what I wanted, files go from:

work-data-sample__0_0_1.csv.gz to data-sample__0_0_1.csv.gz

for files in $(cat listing2.txt) ; do  
    echo "Renaming: $files --> ${files#work-}"
    gsutil mv gs://gs-bucket/jason_testing/$files gs://gs-bucket/jason_testing/${files#work-}
done

for files in $(cat listing2.txt) ; do

echo "Renaming: $files --> ${files#work-}"

gsutil mv gs://gs-bucket/jason_testing/$files gs://gs-bucket/jason_testing/${files#work-}

done

I launched it and noticed something odd, it was only iterating over the list and making one call to the gcloud api to rename the file. This was going to take forever, it actually took 24 hours. I did some reading of the docs and saw that gsutil has a -m option for multiprocessing, I also checked the source code and it looks like gsutil is multiprocess out of the box.

gsutil source code:

should_prohibit_multiprocessing, unused_os = ShouldProhibitMultiprocessing()
if should_prohibit_multiprocessing:
  DEFAULT_PARALLEL_PROCESS_COUNT = 1
  DEFAULT_PARALLEL_THREAD_COUNT = 24
else:
  DEFAULT_PARALLEL_PROCESS_COUNT = min(multiprocessing.cpu_count(), 32)
  DEFAULT_PARALLEL_THREAD_COUNT = 5

should_prohibit_multiprocessing, unused_os = ShouldProhibitMultiprocessing()

if should_prohibit_multiprocessing:

DEFAULT_PARALLEL_PROCESS_COUNT = 1

DEFAULT_PARALLEL_THREAD_COUNT = 24

else:

DEFAULT_PARALLEL_PROCESS_COUNT = min(multiprocessing.cpu_count(), 32)

DEFAULT_PARALLEL_THREAD_COUNT = 5

This is basically saying if the OS can handle multiprocessing, lets spawn the same amount of processes that the system has cpus, and then set the thread count to 5. So my for loop in bash would of taken forever with -m option as well.

So I created some python code that would solve this issue, it would perform all the steps in one, list the files and substring out the filename, and use pythons multiprocessing to spawn 25 workers to do the api calls in chunks. I learned a lot from this and I hope it helps others, I will add comments in the code to show whats going on.

#!/usr/bin/env python3.5
import subprocess
import multiprocessing
import datetime
import shlex


class GsRenamer:
    def __init__(self):
        self.gs_cmd = '/home/jasonr/google-cloud-sdk/bin/gsutil'
        self.file_list = []
        self.final_rename_list = []
        self.now = datetime.datetime.now()

    # method to call subprocess on each command I feed it.
    def execute_jobs(self, cmd):
        try:
            print('{0} INFO: Running rename command: [{1}]'.format(self.now,
                                                                    cmd))
            subprocess.run(shlex.split(cmd), check=True)
        except subprocess.CalledProcessError as e:
            print('[{0}] FATAL: Command failed with error [{1}]').format(cmd,
                                                                         e)

    # method to get all the filenames from the bucket in gcloud and split it to
    # get the filenames. Load the results into a list. filter the list for any
    # blank lines. 
    def get_filenames_from_gs(self):
        cmd = [self.gs_cmd, 'ls',
               'gs://gs-bucket/jason_testing']
        output = subprocess.Popen(cmd, stdout=subprocess.PIPE,
        universal_newlines=True).communicate()[0].splitlines()
        for full_file_path in output:
            file_name  = full_file_path.split('/')[-1]
            self.file_list.append(file_name)
            self.file_list = list(filter(None, self.file_list))


    # method to iterate over the list and create the commands,
    # also leverage pythons string replace to rename the source
    # and target file. build the commands and load into a list.
    # Also use pythons multiprocessing module to spawn 25 processes
    # that hit the api in chunks cutting down the time to rename
    # all the files in place.
    def rename_files(self, string_original, string_replace):
        for files in self.file_list:
            renamed_files = files.replace(string_original,
                                          string_replace)
            rename_command = "{0} mv gs://gs-bucket_testing/{1} " \
                             "gs://gs-bucket/jason_testing/{2}" \
                             .format(self.gs_cmd, files, renamed_files)
            self.final_rename_list.append(rename_command)
        self.final_rename_list.sort()
        multiprocessing.pool = multiprocessing.Pool(
            processes=25)
        multiprocessing.pool.map(self.execute_jobs, self.final_rename_list)


def main():
    gsr = GsRenamer()
    gsr.get_filenames_from_gs()
    gsr.rename_files('work-', '')


if __name__ == "__main__":
    main()

#!/usr/bin/env python3.5

import subprocess

import multiprocessing

import datetime

import shlex

class GsRenamer:

def __init__(self):

self.gs_cmd = '/home/jasonr/google-cloud-sdk/bin/gsutil'

self.file_list = []

self.final_rename_list = []

self.now = datetime.datetime.now()

# method to call subprocess on each command I feed it.

def execute_jobs(self, cmd):

try:

print('{0} INFO: Running rename command: [{1}]'.format(self.now,

cmd))

subprocess.run(shlex.split(cmd), check=True)

except subprocess.CalledProcessError as e:

print('[{0}] FATAL: Command failed with error [{1}]').format(cmd,

# method to get all the filenames from the bucket in gcloud and split it to

# get the filenames. Load the results into a list. filter the list for any

# blank lines.

def get_filenames_from_gs(self):

cmd = [self.gs_cmd, 'ls',

'gs://gs-bucket/jason_testing']

output = subprocess.Popen(cmd, stdout=subprocess.PIPE,

universal_newlines=True).communicate()[0].splitlines()

for full_file_path in output:

file_name = full_file_path.split('/')[-1]

self.file_list.append(file_name)

self.file_list = list(filter(None, self.file_list))

# method to iterate over the list and create the commands,

# also leverage pythons string replace to rename the source

# and target file. build the commands and load into a list.

# Also use pythons multiprocessing module to spawn 25 processes

# that hit the api in chunks cutting down the time to rename

# all the files in place.

def rename_files(self, string_original, string_replace):

for files in self.file_list:

renamed_files = files.replace(string_original,

string_replace)

rename_command = "{0} mv gs://gs-bucket_testing/{1} " \

"gs://gs-bucket/jason_testing/{2}" \

.format(self.gs_cmd, files, renamed_files)

self.final_rename_list.append(rename_command)

self.final_rename_list.sort()

multiprocessing.pool = multiprocessing.Pool(

processes=25)

multiprocessing.pool.map(self.execute_jobs, self.final_rename_list)

def main():

gsr = GsRenamer()

gsr.get_filenames_from_gs()

gsr.rename_files('work-', '')

if __name__ == "__main__":

main()

You can see the process spawns 25 worker processes that will iterate over the list and perform the move in chunks.

jasonr    4612  0.3  0.1 403772 10968 pts/0    Sl+  13:34   0:00  |           \_ python3.5 renamer.py
jasonr    4758  0.0  0.0 176432  8080 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4759  0.0  0.0 176432  8080 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4760  0.0  0.0 176432  8080 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4761  0.0  0.0 176432  8084 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4762  0.0  0.0 176432  8088 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4763  0.0  0.0 176432  8092 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4764  0.0  0.0 176432  8092 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4765  0.0  0.0 176432  8092 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4766  0.0  0.0 176432  8100 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
jasonr    4767  0.0  0.0 176432  8100 pts/0    S+   13:34   0:00  |               \_ python3.5 renamer.py
--SNIP--

jasonr 4612 0.3 0.1 403772 10968 pts/0 Sl+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4758 0.0 0.0 176432 8080 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4759 0.0 0.0 176432 8080 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4760 0.0 0.0 176432 8080 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4761 0.0 0.0 176432 8084 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4762 0.0 0.0 176432 8088 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4763 0.0 0.0 176432 8092 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4764 0.0 0.0 176432 8092 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4765 0.0 0.0 176432 8092 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4766 0.0 0.0 176432 8100 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

jasonr 4767 0.0 0.0 176432 8100 pts/0 S+ 13:34 0:00 | \_ python3.5 renamer.py

--SNIP--

CENTOS6 Postgres pg_upgrade 9 to 11 – In Place – Link – No Copy – Limited Disk Space

April 17, 2019November 13, 2019 admin1 Comment

I wanted to share my experience with upgrading postgres database server from major version 9 to 11. I am showing the steps that I took to get many servers in dev and production upgraded with limited disk space(not enough space to copy). I am hoping this will help with the problems I faced when testing this procedure. Using the –link parameter has drawbacks as noted in the documentation, however we perform full VM backups of each server so we can always restore from backup if the upgrade fails and we will not need to start the pg9.3 database again.

https://www.postgresql.org/docs/11/pgupgrade.html

-k --link

use hard links instead of copying files to the new cluster If you ran pg_upgrade with --link, the data files are shared between the old and new cluster. If you started the new cluster, the new server has written to those shared files and it is unsafe to use the old cluster.

Before we get started make a backup of the files pg_hba.conf and postgresql.conf for later use, you will need to use them later to reconstruct the pg11 configs.

[root@jr-sandbox ~]# cp /data1/data93/pg_hba.conf /root/
[root@jr-sandbox ~]# cp /data1/data93/postgresql.conf /root/

1 2	[root@jr-sandbox ~]# cp /data1/data93/pg_hba.conf /root/ [root@jr-sandbox ~]# cp /data1/data93/postgresql.conf /root/

Use WGET to grab the RPMS from https://yum.postgresql.org

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-11.2-2PGDG.rhel6.x86_64.rpm
[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-contrib-11.2-2PGDG.rhel6.x86_64.rpm
[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-debuginfo-11.2-2PGDG.rhel6.x86_64.rpm
[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-devel-11.2-2PGDG.rhel6.x86_64.rpm
[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-docs-11.2-2PGDG.rhel6.x86_64.rpm
[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-libs-11.2-2PGDG.rhel6.x86_64.rpm
[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-server-11.2-2PGDG.rhel6.x86_64.rpm

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-11.2-2PGDG.rhel6.x86_64.rpm

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-contrib-11.2-2PGDG.rhel6.x86_64.rpm

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-debuginfo-11.2-2PGDG.rhel6.x86_64.rpm

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-devel-11.2-2PGDG.rhel6.x86_64.rpm

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-docs-11.2-2PGDG.rhel6.x86_64.rpm

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-libs-11.2-2PGDG.rhel6.x86_64.rpm

[root@jr-sandbox pg_11]# wget https://yum.postgresql.org/11/redhat/rhel-6-x86_64/postgresql11-server-11.2-2PGDG.rhel6.x86_64.rpm

Install the RPMS for postgres11 that we just downloaded

[root@jr-sandbox pg_11]# rpm -ivh postgresql11-*
warning: postgresql11-11.2-2PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                ########################################### [100%]
   1:postgresql11-libs      ########################################### [ 14%]
   2:postgresql11           ########################################### [ 29%]
   3:postgresql11-contrib   ########################################### [ 43%]
   4:postgresql11-devel     ########################################### [ 57%]
   5:postgresql11-server    ########################################### [ 71%]
   6:postgresql11-docs      ########################################### [ 86%]
   7:postgresql11-debuginfo ########################################### [100%]

[root@jr-sandbox pg_11]# rpm -ivh postgresql11-*

warning: postgresql11-11.2-2PGDG.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

Preparing... ########################################### [100%]

1:postgresql11-libs ########################################### [ 14%]

2:postgresql11 ########################################### [ 29%]

3:postgresql11-contrib ########################################### [ 43%]

4:postgresql11-devel ########################################### [ 57%]

5:postgresql11-server ########################################### [ 71%]

6:postgresql11-docs ########################################### [ 86%]

7:postgresql11-debuginfo ########################################### [100%]

We will create the data location for postgres11 where the files will be hardlinked and not copied. You can see the tablespace disk locations and the index locations from the pg9.3 install. Its important to create the new pg11 data directory on the same filesystem since we will be using the –link parameter and it uses hardlinks which will not traverse filesystems.

[root@jr-sandbox ~]# cd /data1/
[root@jr-sandbox data1]# ls -ltr
total 12
drwx------  3 postgres postgres 4096 Apr 16 22:57 ts_index1
drwx------  3 postgres postgres 4096 Apr 16 22:58 ts_data2
drwx------ 16 postgres postgres 4096 Apr 16 23:02 data93
[root@jr-sandbox data1]# mkdir data11
[root@jr-sandbox data1]# chown -R postgres:postgres data11/

[root@jr-sandbox ~]# cd /data1/

[root@jr-sandbox data1]# ls -ltr

total 12

drwx------ 3 postgres postgres 4096 Apr 16 22:57 ts_index1

drwx------ 3 postgres postgres 4096 Apr 16 22:58 ts_data2

drwx------ 16 postgres postgres 4096 Apr 16 23:02 data93

[root@jr-sandbox data1]# mkdir data11

[root@jr-sandbox data1]# chown -R postgres:postgres data11/

We will need to init a postgres database in our new location on disk data11.

[root@jr-sandbox ~]# su - postgres
-bash-4.1$ /usr/pgsql-11/bin/initdb -D /data1/data11
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data1/data11 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-11/bin/pg_ctl -D /data1/data11 -l logfile start

-bash-4.1$

[root@jr-sandbox ~]# su - postgres

-bash-4.1$ /usr/pgsql-11/bin/initdb -D /data1/data11

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data1/data11 ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/pgsql-11/bin/pg_ctl -D /data1/data11 -l logfile start

-bash-4.1$

Now we are ready to stop pg9.3 and check pg_upgrade compatibility. pg_upgrade ships with a –check argument that will check the compatibility of the clusters and be sure the upgrade will work before changing any files. Lets stop pg9.3 and run the pg_upgrade with the –check parameter.

[root@jr-sandbox ~]# /etc/init.d/postgresql-9.3 stop
Stopping postgresql-9.3 service:                           [  OK  ]

[root@jr-sandbox ~]# su - postgres

-bash-4.1$ /usr/pgsql-11/bin/pg_upgrade --link --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/data1/data93/ --new-datadir=/data1/data11/ --check
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for hash indexes                                   ok
Checking for roles starting with "pg_"                      ok
Checking for incompatible "line" data type                  ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

[root@jr-sandbox ~]# /etc/init.d/postgresql-9.3 stop

Stopping postgresql-9.3 service: [ OK ]

[root@jr-sandbox ~]# su - postgres

-bash-4.1$ /usr/pgsql-11/bin/pg_upgrade --link --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/data1/data93/ --new-datadir=/data1/data11/ --check

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for invalid "unknown" user columns ok

Checking for hash indexes ok

Checking for roles starting with "pg_" ok

Checking for incompatible "line" data type ok

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

*Clusters are compatible*

Ok checks have passed and the cluster versions are ready for upgrade, lets run this without the –check parameter and upgrade postgres.

[root@jr-sandbox ~]# su - postgres
-bash-4.1$ /usr/pgsql-11/bin/pg_upgrade --link --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/data1/data93/ --new-datadir=/data1/data11/
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for roles starting with "pg_"                      ok
Checking for incompatible "line" data type                  ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /data1/data93/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok
Checking for hash indexes                                   ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
-bash-4.1$

[root@jr-sandbox ~]# su - postgres

-bash-4.1$ /usr/pgsql-11/bin/pg_upgrade --link --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/data1/data93/ --new-datadir=/data1/data11/

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for invalid "unknown" user columns ok

Checking for roles starting with "pg_" ok

Checking for incompatible "line" data type ok

Creating dump of global objects ok

Creating dump of database schemas

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

Performing Upgrade

------------------

Analyzing all rows in the new cluster ok

Freezing all rows in the new cluster ok

Deleting files from new pg_xact ok

Copying old pg_clog to new server ok

Setting next transaction ID and epoch for new cluster ok

Deleting files from new pg_multixact/offsets ok

Copying old pg_multixact/offsets to new server ok

Deleting files from new pg_multixact/members ok

Copying old pg_multixact/members to new server ok

Setting next multixact ID and offset for new cluster ok

Resetting WAL archives ok

Setting frozenxid and minmxid counters in new cluster ok

Restoring global objects in the new cluster ok

Restoring database schemas in the new cluster

Adding ".old" suffix to old global/pg_control ok

If you want to start the old cluster, you will need to remove

the ".old" suffix from /data1/data93/global/pg_control.old.

Because "link" mode was used, the old cluster cannot be safely

started once the new cluster has been started.

Linking user relation files

Setting next OID for new cluster ok

Sync data directory to disk ok

Creating script to analyze new cluster ok

Creating script to delete old cluster ok

Checking for hash indexes ok

Upgrade Complete

----------------

Optimizer statistics are not transferred by pg_upgrade so,

once you start the new server, consider running:

./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:

./delete_old_cluster.sh

-bash-4.1$

OK the pg_upgrade code completed successfully and has generated 2 scripts. One to analyze the new pg11 cluster to get stats for the query planner and vacuum. The other to cleanup and remove the old pg9.3 locations on disk. Let’s start pg11, we will need to create an override file to tell pg11 where the data11 data lives, then we should be able to start postgres and check some things and verify our upgrade.

[root@jr-sandbox ~]# cd /etc/sysconfig/pgsql/
[root@jr-sandbox pgsql]# cp postgresql-9.3 postgresql-11
[root@jr-sandbox pgsql]# vim postgresql-11 
[root@jr-sandbox pgsql]# cat postgresql-11 
PGDATA=/data1/data11
PGLOG=/data1/data11/pgstartup.log

[root@jr-sandbox pgsql]# /etc/init.d/postgresql-11 start
Starting postgresql-11 service:                            [  OK  ]

[root@jr-sandbox ~]# cd /etc/sysconfig/pgsql/

[root@jr-sandbox pgsql]# cp postgresql-9.3 postgresql-11

[root@jr-sandbox pgsql]# vim postgresql-11

[root@jr-sandbox pgsql]# cat postgresql-11

PGDATA=/data1/data11

PGLOG=/data1/data11/pgstartup.log

[root@jr-sandbox pgsql]# /etc/init.d/postgresql-11 start

Starting postgresql-11 service: [ OK ]

[root@jr-sandbox pgsql]# su - postgres
-bash-4.1$ ps -ef| grep postgres| head -n 1
postgres 31047     1  0 23:30 ?        00:00:00 /usr/pgsql-11/bin/postmaster -D /data1/data11
-bash-4.1$ psql 
psql (11.2)
Type "help" for help.

postgres=# select spcname
      ,pg_tablespace_location(oid) 
from   pg_tablespace;
  spcname   | pg_tablespace_location 
------------+------------------------
 pg_default | 
 pg_global  | 
 index1     | /data1/ts_index1
 data2      | /data1/ts_data2
(4 rows)

[root@jr-sandbox pgsql]# su - postgres

-bash-4.1$ ps -ef| grep postgres| head -n 1

postgres 31047 1 0 23:30 ? 00:00:00 /usr/pgsql-11/bin/postmaster -D /data1/data11

-bash-4.1$ psql

psql (11.2)

Type "help" for help.

postgres=# select spcname

,pg_tablespace_location(oid)

from pg_tablespace;

spcname | pg_tablespace_location

------------+------------------------

pg_default |

pg_global |

index1 | /data1/ts_index1

data2 | /data1/ts_data2

(4 rows)

OK we can see we have pg11 running and we can run the generated scripts to cleanup, but lets take a look at the data and index directories to see what the upgrade produced.

[root@jr-sandbox ~]# cd /data1/
[root@jr-sandbox data1]# ls
data11  data93  ts_data2  ts_index1
[root@jr-sandbox data1]# cd data11/
[root@jr-sandbox data11]# ls -l
total 132
drwx------ 5 postgres postgres  4096 Apr 16 23:19 base
-rw------- 1 postgres postgres    30 Apr 16 23:30 current_logfiles
drwx------ 2 postgres postgres  4096 Apr 16 23:32 global
drwx------ 2 postgres postgres  4096 Apr 16 23:10 log
drwx------ 2 postgres postgres  4096 Apr 16 23:09 pg_commit_ts
drwx------ 2 postgres postgres  4096 Apr 16 23:09 pg_dynshmem
-rw------- 1 postgres postgres  4513 Apr 16 23:09 pg_hba.conf
-rw------- 1 postgres postgres  1636 Apr 16 23:09 pg_ident.conf
drwx------ 4 postgres postgres  4096 Apr 16 23:35 pg_logical
drwx------ 4 postgres postgres  4096 Apr 16 23:19 pg_multixact
drwx------ 2 postgres postgres  4096 Apr 16 23:30 pg_notify
drwx------ 2 postgres postgres  4096 Apr 16 23:09 pg_replslot
drwx------ 2 postgres postgres  4096 Apr 16 23:09 pg_serial
drwx------ 2 postgres postgres  4096 Apr 16 23:09 pg_snapshots
-rw------- 1 postgres postgres   469 Apr 16 23:30 pgstartup.log
drwx------ 2 postgres postgres  4096 Apr 16 23:30 pg_stat
drwx------ 2 postgres postgres  4096 Apr 16 23:45 pg_stat_tmp
drwx------ 2 postgres postgres  4096 Apr 16 23:09 pg_subtrans
drwx------ 2 postgres postgres  4096 Apr 16 23:19 pg_tblspc
drwx------ 2 postgres postgres  4096 Apr 16 23:09 pg_twophase
-rw------- 1 postgres postgres     3 Apr 16 23:09 PG_VERSION
drwx------ 3 postgres postgres  4096 Apr 16 23:19 pg_wal
drwx------ 2 postgres postgres  4096 Apr 16 23:19 pg_xact
-rw------- 1 postgres postgres    88 Apr 16 23:09 postgresql.auto.conf
-rw------- 1 postgres postgres 23863 Apr 16 23:09 postgresql.conf
-rw------- 1 postgres postgres    48 Apr 16 23:30 postmaster.opts
-rw------- 1 postgres postgres    95 Apr 16 23:30 postmaster.pid
[root@jr-sandbox data11]# cd ../ts_index1/
[root@jr-sandbox ts_index1]# ls -l
total 8
drwx------ 2 postgres postgres 4096 Apr 16 23:19 PG_11_201809051
drwx------ 2 postgres postgres 4096 Apr 16 22:57 PG_9.3_201306121
[root@jr-sandbox ts_index1]# cd ../ts_data2/
You have mail in /var/spool/mail/root
[root@jr-sandbox ts_data2]# ls -l
total 8
drwx------ 2 postgres postgres 4096 Apr 16 23:19 PG_11_201809051
drwx------ 2 postgres postgres 4096 Apr 16 22:58 PG_9.3_201306121
<strong>

[root@jr-sandbox ~]# cd /data1/

[root@jr-sandbox data1]# ls

data11 data93 ts_data2 ts_index1

[root@jr-sandbox data1]# cd data11/

[root@jr-sandbox data11]# ls -l

total 132

drwx------ 5 postgres postgres 4096 Apr 16 23:19 base

-rw------- 1 postgres postgres 30 Apr 16 23:30 current_logfiles

drwx------ 2 postgres postgres 4096 Apr 16 23:32 global

drwx------ 2 postgres postgres 4096 Apr 16 23:10 log

drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_commit_ts

drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_dynshmem

-rw------- 1 postgres postgres 4513 Apr 16 23:09 pg_hba.conf

-rw------- 1 postgres postgres 1636 Apr 16 23:09 pg_ident.conf

drwx------ 4 postgres postgres 4096 Apr 16 23:35 pg_logical

drwx------ 4 postgres postgres 4096 Apr 16 23:19 pg_multixact

drwx------ 2 postgres postgres 4096 Apr 16 23:30 pg_notify

drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_replslot

drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_serial

drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_snapshots

-rw------- 1 postgres postgres 469 Apr 16 23:30 pgstartup.log

drwx------ 2 postgres postgres 4096 Apr 16 23:30 pg_stat

drwx------ 2 postgres postgres 4096 Apr 16 23:45 pg_stat_tmp

drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_subtrans

drwx------ 2 postgres postgres 4096 Apr 16 23:19 pg_tblspc

drwx------ 2 postgres postgres 4096 Apr 16 23:09 pg_twophase

-rw------- 1 postgres postgres 3 Apr 16 23:09 PG_VERSION

drwx------ 3 postgres postgres 4096 Apr 16 23:19 pg_wal

drwx------ 2 postgres postgres 4096 Apr 16 23:19 pg_xact

-rw------- 1 postgres postgres 88 Apr 16 23:09 postgresql.auto.conf

-rw------- 1 postgres postgres 23863 Apr 16 23:09 postgresql.conf

-rw------- 1 postgres postgres 48 Apr 16 23:30 postmaster.opts

-rw------- 1 postgres postgres 95 Apr 16 23:30 postmaster.pid

[root@jr-sandbox data11]# cd ../ts_index1/

[root@jr-sandbox ts_index1]# ls -l

total 8

drwx------ 2 postgres postgres 4096 Apr 16 23:19 PG_11_201809051

drwx------ 2 postgres postgres 4096 Apr 16 22:57 PG_9.3_201306121

[root@jr-sandbox ts_index1]# cd ../ts_data2/

You have mail in /var/spool/mail/root

[root@jr-sandbox ts_data2]# ls -l

total 8

drwx------ 2 postgres postgres 4096 Apr 16 23:19 PG_11_201809051

drwx------ 2 postgres postgres 4096 Apr 16 22:58 PG_9.3_201306121

We can view the shell scripts that pg_upgrade produced and cleanup the old pg9.3 references and run the analyze vacuums.

[root@jr-sandbox ~]# su - postgres
-bash-4.1$ ls
11  9.3  analyze_new_cluster.sh  delete_old_cluster.sh
-bash-4.1$ cat delete_old_cluster.sh 
#!/bin/sh

rm -rf '/data1/data93'
rm -rf '/data1/ts_index1/PG_9.3_201306121'
rm -rf '/data1/ts_data2/PG_9.3_201306121'
-bash-4.1$ cat analyze_new_cluster.sh 
#!/bin/sh

echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy.  When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo

echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics generation.'
echo

echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo '    "/usr/pgsql-11/bin/vacuumdb" --all --analyze-only'
echo

"/usr/pgsql-11/bin/vacuumdb" --all --analyze-in-stages
echo

echo 'Done'

[root@jr-sandbox ~]# su - postgres

-bash-4.1$ ls

11 9.3 analyze_new_cluster.sh delete_old_cluster.sh

-bash-4.1$ cat delete_old_cluster.sh

#!/bin/sh

rm -rf '/data1/data93'

rm -rf '/data1/ts_index1/PG_9.3_201306121'

rm -rf '/data1/ts_data2/PG_9.3_201306121'

-bash-4.1$ cat analyze_new_cluster.sh

#!/bin/sh

echo 'This script will generate minimal optimizer statistics rapidly'

echo 'so your system is usable, and then gather statistics twice more'

echo 'with increasing accuracy. When it is done, your system will'

echo 'have the default level of optimizer statistics.'

echo

echo 'If you have used ALTER TABLE to modify the statistics target for'

echo 'any tables, you might want to remove them and restore them after'

echo 'running this script because they will delay fast statistics generation.'

echo

echo 'If you would like default statistics as quickly as possible, cancel'

echo 'this script and run:'

echo ' "/usr/pgsql-11/bin/vacuumdb" --all --analyze-only'

echo

"/usr/pgsql-11/bin/vacuumdb" --all --analyze-in-stages

echo

echo 'Done'

This looks good, lets execute them and cleanup any pg9.3 references as well as remove the pg9.3 rpms.

[root@jr-sandbox data1]# su - postgres
-bash-4.1$ ./delete_old_cluster.sh 
-bash-4.1$ ./analyze_new_cluster.sh 
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
    "/usr/pgsql-11/bin/vacuumdb" --all --analyze-only

vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Done
-bash-4.1$

[root@jr-sandbox data1]# su - postgres

-bash-4.1$ ./delete_old_cluster.sh

-bash-4.1$ ./analyze_new_cluster.sh

This script will generate minimal optimizer statistics rapidly

so your system is usable, and then gather statistics twice more

with increasing accuracy. When it is done, your system will

have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for

any tables, you might want to remove them and restore them after

running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel

this script and run:

"/usr/pgsql-11/bin/vacuumdb" --all --analyze-only

vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)

vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)

vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)

vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)

vacuumdb: processing database "postgres": Generating default (full) optimizer statistics

vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Done

-bash-4.1$

Remove the pg9.3 rpms and references, set the new data location in the .pgsql_profile.

[root@jr-sandbox ~]# yum remove postgresql93*
Loaded plugins: fastestmirror
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-contrib.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-debuginfo.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-devel.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-docs.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-libs.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
---> Package postgresql93-server.x86_64 0:9.3.24-1PGDG.rhel6 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================================================================================================================================
 Package                                                  Arch                                     Version                                              Repository                                            Size
===================================================================================================================================================================================================================
Removing:
 postgresql93                                             x86_64                                   9.3.24-1PGDG.rhel6                                   @affinity6-prod-db                                   5.3 M
 postgresql93-contrib                                     x86_64                                   9.3.24-1PGDG.rhel6                                   @affinity6-prod-db                                   1.7 M
 postgresql93-debuginfo                                   x86_64                                   9.3.24-1PGDG.rhel6                                   @affinity6-prod-db                                    28 M
 postgresql93-devel                                       x86_64                                   9.3.24-1PGDG.rhel6                                   @affinity6-prod-db                                   6.8 M
 postgresql93-docs                                        x86_64                                   9.3.24-1PGDG.rhel6                                   @affinity6-prod-db                                    31 M
 postgresql93-libs                                        x86_64                                   9.3.24-1PGDG.rhel6                                   @affinity6-prod-db                                   632 k
 postgresql93-server                                      x86_64                                   9.3.24-1PGDG.rhel6                                   @affinity6-prod-db                                    16 M

Transaction Summary
===================================================================================================================================================================================================================
Remove        7 Package(s)

Installed size: 89 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Erasing    : postgresql93-debuginfo-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                1/7 
  Erasing    : postgresql93-devel-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                    2/7 
  Erasing    : postgresql93-server-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                   3/7 
  Erasing    : postgresql93-contrib-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                  4/7 
  Erasing    : postgresql93-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                          5/7 
  Erasing    : postgresql93-libs-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                     6/7 
  Erasing    : postgresql93-docs-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                     7/7 
  Verifying  : postgresql93-debuginfo-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                1/7 
  Verifying  : postgresql93-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                          2/7 
  Verifying  : postgresql93-docs-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                     3/7 
  Verifying  : postgresql93-contrib-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                  4/7 
  Verifying  : postgresql93-server-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                   5/7 
  Verifying  : postgresql93-devel-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                    6/7 
  Verifying  : postgresql93-libs-9.3.24-1PGDG.rhel6.x86_64                                                                                                                                                     7/7 

Removed:
  postgresql93.x86_64 0:9.3.24-1PGDG.rhel6          postgresql93-contrib.x86_64 0:9.3.24-1PGDG.rhel6     postgresql93-debuginfo.x86_64 0:9.3.24-1PGDG.rhel6     postgresql93-devel.x86_64 0:9.3.24-1PGDG.rhel6    
  postgresql93-docs.x86_64 0:9.3.24-1PGDG.rhel6     postgresql93-libs.x86_64 0:9.3.24-1PGDG.rhel6        postgresql93-server.x86_64 0:9.3.24-1PGDG.rhel6       

Complete!
[root@jr-sandbox ~]#

[root@jr-sandbox ~]# yum remove postgresql93*

Loaded plugins: fastestmirror

Setting up Remove Process

Resolving Dependencies

--> Running transaction check

---> Package postgresql93.x86_64 0:9.3.24-1PGDG.rhel6 will be erased

---> Package postgresql93-contrib.x86_64 0:9.3.24-1PGDG.rhel6 will be erased

---> Package postgresql93-debuginfo.x86_64 0:9.3.24-1PGDG.rhel6 will be erased

---> Package postgresql93-devel.x86_64 0:9.3.24-1PGDG.rhel6 will be erased

---> Package postgresql93-docs.x86_64 0:9.3.24-1PGDG.rhel6 will be erased

---> Package postgresql93-libs.x86_64 0:9.3.24-1PGDG.rhel6 will be erased

---> Package postgresql93-server.x86_64 0:9.3.24-1PGDG.rhel6 will be erased

--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================================================================================================================================

Package Arch Version Repository Size

Removing:

postgresql93 x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 5.3 M

postgresql93-contrib x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 1.7 M

postgresql93-debuginfo x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 28 M

postgresql93-devel x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 6.8 M

postgresql93-docs x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 31 M

postgresql93-libs x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 632 k

postgresql93-server x86_64 9.3.24-1PGDG.rhel6 @affinity6-prod-db 16 M

Transaction Summary

Remove 7 Package(s)

Installed size: 89 M

Is this ok [y/N]: y

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Warning: RPMDB altered outside of yum.

Erasing : postgresql93-debuginfo-9.3.24-1PGDG.rhel6.x86_64 1/7

Erasing : postgresql93-devel-9.3.24-1PGDG.rhel6.x86_64 2/7

Erasing : postgresql93-server-9.3.24-1PGDG.rhel6.x86_64 3/7

Erasing : postgresql93-contrib-9.3.24-1PGDG.rhel6.x86_64 4/7

Erasing : postgresql93-9.3.24-1PGDG.rhel6.x86_64 5/7

Erasing : postgresql93-libs-9.3.24-1PGDG.rhel6.x86_64 6/7

Erasing : postgresql93-docs-9.3.24-1PGDG.rhel6.x86_64 7/7

Verifying : postgresql93-debuginfo-9.3.24-1PGDG.rhel6.x86_64 1/7

Verifying : postgresql93-9.3.24-1PGDG.rhel6.x86_64 2/7

Verifying : postgresql93-docs-9.3.24-1PGDG.rhel6.x86_64 3/7

Verifying : postgresql93-contrib-9.3.24-1PGDG.rhel6.x86_64 4/7

Verifying : postgresql93-server-9.3.24-1PGDG.rhel6.x86_64 5/7

Verifying : postgresql93-devel-9.3.24-1PGDG.rhel6.x86_64 6/7

Verifying : postgresql93-libs-9.3.24-1PGDG.rhel6.x86_64 7/7

Removed:

postgresql93.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-contrib.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-debuginfo.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-devel.x86_64 0:9.3.24-1PGDG.rhel6

postgresql93-docs.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-libs.x86_64 0:9.3.24-1PGDG.rhel6 postgresql93-server.x86_64 0:9.3.24-1PGDG.rhel6

Complete!

[root@jr-sandbox ~]#

[root@jr-sandbox ~]# cd /etc/sysconfig/pgsql/
You have new mail in /var/spool/mail/root
[root@jr-sandbox pgsql]# ls
postgresql-11  postgresql-9.3
[root@jr-sandbox pgsql]# rm -f postgresql-9.3 
[root@jr-sandbox pgsql]# su - postgres
-bash-4.1$ ls
11  9.3  analyze_new_cluster.sh  delete_old_cluster.sh
-bash-4.1$ rm -rf 9.3

[root@jr-sandbox ~]# cd /etc/sysconfig/pgsql/

You have new mail in /var/spool/mail/root

[root@jr-sandbox pgsql]# ls

postgresql-11 postgresql-9.3

[root@jr-sandbox pgsql]# rm -f postgresql-9.3

[root@jr-sandbox pgsql]# su - postgres

-bash-4.1$ ls

11 9.3 analyze_new_cluster.sh delete_old_cluster.sh

-bash-4.1$ rm -rf 9.3

You can now view the pg_hba.conf and postgresql.conf you saved in /root and add whats needed to the new pg11 configs.

That’s it!!

SINOPIA NPM allow connections to GITHUB as well as the NPM registry

October 11, 2018October 11, 2018 adminLeave a comment

SINOPIA LINK HERE
We use SINOPIA as a proxy on our internal network behind the firewall to allow users to install NODE packages without an internet connection. We basically run sinopia on a machine that has access to the internet and the clients point to the server to install packages that are not locally available. We have been running into issues where installs that needed access to github would fail with something like this:

[15:29:07] user1@sb-user1:~/app/mc_api/lib/reports $ npm install --save slack/client --loglevel verbose
npm info it worked if it ends with ok
npm verb cli [ '/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/node',
npm verb cli   '/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/npm',
npm verb cli   'install',
npm verb cli   '--save',
npm verb cli   'slack/client',
npm verb cli   '--loglevel',
npm verb cli   'verbose' ]
npm info using npm@6.4.1
npm info using node@v8.9.4
npm verb npm-session f40f30f7bf0339f9
npm timing stage:rollbackFailedOptional Completed in 1ms
npm timing stage:runTopLevelLifecycles Completed in 1114ms
npm verb stack Error: exited with error code: 128
npm verb stack     at ChildProcess.<anonymous> (/home/user1/node_local_install/.nvm/versions/node/v8.9.4/lib/node_modules/npm/node_modules/pacote/lib/util/finished.js:12:19)
npm verb stack     at emitTwo (events.js:126:13)
npm verb stack     at ChildProcess.emit (events.js:214:7)
npm verb stack     at maybeClose (internal/child_process.js:925:16)
npm verb stack     at Socket.stream.socket.on (internal/child_process.js:346:11)
npm verb stack     at emitOne (events.js:116:13)
npm verb stack     at Socket.emit (events.js:211:7)
npm verb stack     at Pipe._handle.close [as _onclose] (net.js:554:12)
npm verb cwd /home/user1/app/mc_api/lib/reports
npm verb Linux 2.6.32-754.3.5.el6.x86_64
npm verb argv "/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/node" "/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/npm" "install" "--save" "slack/client" "--loglevel" "verbose"
npm verb node v8.9.4
npm verb npm  v6.4.1
npm ERR! Error while executing:
npm ERR! /usr/bin/git ls-remote -h -t ssh://git@github.com/slack/client.git
npm ERR!
npm ERR! ssh: connect to host github.com port 22: Connection refused
npm ERR! fatal: Could not read from remote repository.
npm ERR!
npm ERR! Please make sure you have the correct access rights
npm ERR! and the repository exists.
npm ERR!
npm ERR! exited with error code: 128
npm verb exit [ 1, true ]
npm timing npm Completed in 1497ms

npm ERR! A complete log of this run can be found in:
npm ERR!     /home/user1/.npm/_logs/2018-10-10T19_34_06_306Z-debug.log

[15:29:07] user1@sb-user1:~/app/mc_api/lib/reports $ npm install --save slack/client --loglevel verbose

npm info it worked if it ends with ok

npm verb cli [ '/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/node',

npm verb cli '/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/npm',

npm verb cli 'install',

npm verb cli '--save',

npm verb cli 'slack/client',

npm verb cli '--loglevel',

npm verb cli 'verbose' ]

npm info using npm@6.4.1

npm info using node@v8.9.4

npm verb npm-session f40f30f7bf0339f9

npm timing stage:rollbackFailedOptional Completed in 1ms

npm timing stage:runTopLevelLifecycles Completed in 1114ms

npm verb stack Error: exited with error code: 128

npm verb stack at ChildProcess.<anonymous> (/home/user1/node_local_install/.nvm/versions/node/v8.9.4/lib/node_modules/npm/node_modules/pacote/lib/util/finished.js:12:19)

npm verb stack at emitTwo (events.js:126:13)

npm verb stack at ChildProcess.emit (events.js:214:7)

npm verb stack at maybeClose (internal/child_process.js:925:16)

npm verb stack at Socket.stream.socket.on (internal/child_process.js:346:11)

npm verb stack at emitOne (events.js:116:13)

npm verb stack at Socket.emit (events.js:211:7)

npm verb stack at Pipe._handle.close [as _onclose] (net.js:554:12)

npm verb cwd /home/user1/app/mc_api/lib/reports

npm verb Linux 2.6.32-754.3.5.el6.x86_64

npm verb argv "/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/node" "/home/user1/node_local_install/.nvm/versions/node/v8.9.4/bin/npm" "install" "--save" "slack/client" "--loglevel" "verbose"

npm verb node v8.9.4

npm verb npm v6.4.1

npm ERR! Error while executing:

npm ERR! /usr/bin/git ls-remote -h -t ssh://[email protected]/slack/client.git

npm ERR!

npm ERR! ssh: connect to host github.com port 22: Connection refused

npm ERR! fatal: Could not read from remote repository.

npm ERR!

npm ERR! Please make sure you have the correct access rights

npm ERR! and the repository exists.

npm ERR!

npm ERR! exited with error code: 128

npm verb exit [ 1, true ]

npm timing npm Completed in 1497ms

npm ERR! A complete log of this run can be found in:

npm ERR! /home/user1/.npm/_logs/2018-10-10T19_34_06_306Z-debug.log

As you can see, we are getting choked at:

npm ERR! ssh: connect to host github.com port 22: Connection refused
npm ERR! fatal: Could not read from remote repository.

1 2	npm ERR! ssh: connect to host github.com port 22: Connection refused npm ERR! fatal: Could not read from remote repository.

To get around this we need to change the config.yml on the server to allow proxies to github, here is the final configuration. Hope this helps other users as we had a fun time trying to figure it out. Pay attention to the uplinks section and the proxy requests where github is defined.

#
# This is the default config file. It allows all users to do anything,
# so don't use it on production systems.
#
# Look here for more config file examples:
# https://github.com/rlidwka/sinopia/tree/master/conf
#

# path to a directory with all packages
storage: ./storage

auth:
  htpasswd:
    file: ./htpasswd
    # Maximum amount of users allowed to register, defaults to "+inf".
    # You can set this to -1 to disable registration.
    #max_users: 1000

# a list of other known repositories we can talk to
uplinks:
  npmjs:
    url: https://registry.npmjs.org/
  github:
    url: https://github.com/

packages:
  '@*/*':
    # scoped packages
    access: $all
    publish: $authenticated
    proxy:
      - npmjs
      - github


  '*':
    # allow all users (including non-authenticated users) to read and
    # publish all packages
    #
    # you can specify usernames/groupnames (depending on your auth plugin)
    # and three keywords: "$all", "$anonymous", "$authenticated"
    access: $all

    # allow all known users to publish packages
    # (anyone can register by default, remember?)
    publish: $authenticated

    # if package is not available locally, proxy requests to 'npmjs' registry
    proxy:
      - npmjs
      - github
# log settings
logs:
  #- {type: stdout, format: pretty, level: http}
  - {type: file, path: sinopia.log, level: debug}

#Bind Address
listen:
  - 0.0.0.0:4873
#

# This is the default config file. It allows all users to do anything,

# so don't use it on production systems.

# Look here for more config file examples:

# https://github.com/rlidwka/sinopia/tree/master/conf

# path to a directory with all packages

storage: ./storage

auth:

htpasswd:

file: ./htpasswd

# Maximum amount of users allowed to register, defaults to "+inf".

# You can set this to -1 to disable registration.

#max_users: 1000

# a list of other known repositories we can talk to

uplinks:

npmjs:

url: https://registry.npmjs.org/

github:

url: https://github.com/

packages:

'@*/*':

# scoped packages

access: $all

publish: $authenticated

proxy:

- npmjs

- github

'*':

# allow all users (including non-authenticated users) to read and

# publish all packages

# you can specify usernames/groupnames (depending on your auth plugin)

# and three keywords: "$all", "$anonymous", "$authenticated"

access: $all

# allow all known users to publish packages

# (anyone can register by default, remember?)

publish: $authenticated

# if package is not available locally, proxy requests to 'npmjs' registry

proxy:

- npmjs

- github

# log settings

logs:

#- {type: stdout, format: pretty, level: http}

- {type: file, path: sinopia.log, level: debug}

#Bind Address

listen:

- 0.0.0.0:4873

Python Generator Find Files With Wildcard

February 13, 2018February 13, 2018 adminLeave a comment

This is a neat way to generate file names in a directory that match a specific pattern, I use this to generate a list of files exported out of hive to load into S3.

def find_files(directory, pattern):
    for root, dirs, files in os.walk(directory):
        for basename in sorted(files):
            if fnmatch.fnmatch(basename, pattern):
                filename = os.path.join(root, basename)
                yield filename

def find_files(directory, pattern):

for root, dirs, files in os.walk(directory):

for basename in sorted(files):

if fnmatch.fnmatch(basename, pattern):

filename = os.path.join(root, basename)

yield filename

local_dir = '/mnt/share/etl/date/'
for files in find_files(local_dir,'*.gz'):
    key = files[1:]
    try:
        awss3.upload(key,files)
        log_msg = ('uploading file: [{0}] to S3').format(files)
        log.write(log_msg)
    except Exception as e:
        log_msg = ('ERROR: {0} uploading file: [{0}] to S3').format(e,files)
        log.write(log_msg, 'error')

local_dir = '/mnt/share/etl/date/'

for files in find_files(local_dir,'*.gz'):

key = files[1:]

try:

awss3.upload(key,files)

log_msg = ('uploading file: [{0}] to S3').format(files)

log.write(log_msg)

except Exception as e:

log_msg = ('ERROR: {0} uploading file: [{0}] to S3').format(e,files)

log.write(log_msg, 'error')

POSTGRES – Top 100 Tables In Tablespace

May 30, 2017May 30, 2017 adminLeave a comment

I had a situation where I needed to find the top 100 largest tables in a certain tablespace on a postgres 9 database, in my case we archive tables into an archive1 tablespace. This query will find all the largest relations in the archive1 tablespace. Its important to swap out ‘archive1’ with whatever tablespace you are trying to list.

SELECT N.nspname || '.' || C.relname AS "relation",
    CASE WHEN reltype = 0
        THEN pg_size_pretty(pg_total_relation_size(C.oid)) || ' (index)'
        ELSE pg_size_pretty(pg_total_relation_size(C.oid)) || ' (' ||  pg_size_pretty(pg_relation_size(C.oid)) || ' data)'
    END AS "size (data)",
    COALESCE(T.tablespace, I.tablespace, '') AS "tablespace"
FROM pg_class C
LEFT JOIN pg_namespace N ON  (N.oid = C.relnamespace)
LEFT JOIN pg_tables T ON (T.tablename = C.relname)
LEFT JOIN pg_indexes I ON (I.indexname = C.relname)
LEFT JOIN pg_tablespace TS ON TS.spcname = T.tablespace
LEFT JOIN pg_tablespace XS ON XS.spcname = I.tablespace
WHERE nspname NOT IN ('pg_catalog','pg_toast','information_schema') 
AND COALESCE(T.tablespace, I.tablespace, '') = 'archive1'
ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 100;
                          relation                          |         size (data)          | tablespace
------------------------------------------------------------+------------------------------+------------
 public.table1                                              | 172 GB (148 GB data)         | archive1
 public.table2                                              | 171 GB (147 GB data)         | archive1
 public.table3                                              | 32 GB (32 GB data)           | archive1

--SNIP--

SELECT N.nspname || '.' || C.relname AS "relation",

CASE WHEN reltype = 0

THEN pg_size_pretty(pg_total_relation_size(C.oid)) || ' (index)'

ELSE pg_size_pretty(pg_total_relation_size(C.oid)) || ' (' || pg_size_pretty(pg_relation_size(C.oid)) || ' data)'

END AS "size (data)",

COALESCE(T.tablespace, I.tablespace, '') AS "tablespace"

FROM pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

LEFT JOIN pg_tables T ON (T.tablename = C.relname)

LEFT JOIN pg_indexes I ON (I.indexname = C.relname)

LEFT JOIN pg_tablespace TS ON TS.spcname = T.tablespace

LEFT JOIN pg_tablespace XS ON XS.spcname = I.tablespace

WHERE nspname NOT IN ('pg_catalog','pg_toast','information_schema')

AND COALESCE(T.tablespace, I.tablespace, '') = 'archive1'

ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 100;

relation | size (data) | tablespace

------------------------------------------------------------+------------------------------+------------

public.table1 | 172 GB (148 GB data) | archive1

public.table2 | 171 GB (147 GB data) | archive1

public.table3 | 32 GB (32 GB data) | archive1

--SNIP--

Hope this helps you out, took some time to get it to work.

Python Backup WORDPRESS Site / DATABASE and HTML

May 7, 2016May 7, 2016 adminLeave a comment

I have this blog hosted on a LINODE dedicated LINUX server. It’s about 10 dollars a month for a 1 core system with about 250GB of disk space and 1GB of RAM, this server runs the common LAMP stack, I needed a quick and dirty script to backup MYSQL database and the PHP code contained in the /var/www/html folder. I wanted the script to compress the contents of both and move them into a directory with the correct date. See the comments below outlining the code and the action of running it.

# Linux server kernel version details.
[22:47:51] root@jasonralph:~/py_backup_site_full # uname -a
Linux jasonralph.jasonralph.org 4.5.0-x86_64-linode65 #2 SMP Mon Mar 14 18:01:58 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

# Linux distro information
[22:52:15] root@jasonralph:~/py_backup_site_full # cat /etc/centos-release
CentOS release 6.7 (Final)

# Free output
[22:59:58] root@jasonralph:~/py_backup_site_full # free -m
             total       used       free     shared    buffers     cached
Mem:           991        796        195          0         62        329
-/+ buffers/cache:        403        587
Swap:          511          0        511

# Here you can see the python binary location on the filesystem.
[22:46:36] root@jasonralph:~/py_backup_site_full # which python
/usr/local/bin/python

# Now you can see the python version I am using. 
[22:46:50] root@jasonralph:~/py_backup_site_full # python --version
Python 2.7.6

# Long listing of the backup folder I have setup. 
[22:47:48] root@jasonralph:~/py_backup_site_full # ls -l
total 4
-rwxr-xr-x 1 root root 1560 May  7 22:46 wordpress_backup.py

# Linux server kernel version details.

[22:47:51] root@jasonralph:~/py_backup_site_full # uname -a

Linux jasonralph.jasonralph.org 4.5.0-x86_64-linode65 #2 SMP Mon Mar 14 18:01:58 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

# Linux distro information

[22:52:15] root@jasonralph:~/py_backup_site_full # cat /etc/centos-release

CentOS release 6.7 (Final)

# Free output

[22:59:58] root@jasonralph:~/py_backup_site_full # free -m

total used free shared buffers cached

Mem: 991 796 195 0 62 329

-/+ buffers/cache: 403 587

Swap: 511 0 511

# Here you can see the python binary location on the filesystem.

[22:46:36] root@jasonralph:~/py_backup_site_full # which python

/usr/local/bin/python

# Now you can see the python version I am using.

[22:46:50] root@jasonralph:~/py_backup_site_full # python --version

Python 2.7.6

# Long listing of the backup folder I have setup.

[22:47:48] root@jasonralph:~/py_backup_site_full # ls -l

total 4

-rwxr-xr-x 1 root root 1560 May 7 22:46 wordpress_backup.py

# cat of the python script to see the source code. 

[23:02:39] root@jasonralph:~/py_backup_site_full # cat wordpress_backup.py
#!/usr/local/bin/python
import optparse
import os
import datetime
import shutil
from subprocess import Popen, PIPE

date = datetime.datetime.now().strftime('%Y%m%d-%s')
f_date = datetime.datetime.now().strftime('%Y%m%d')

def backup_all_databases():
    args = ['mysqldump', '-u', 'root', '-pPASSWORD', '--all-databases']
    with open("%s.sql.gz" % f_date, 'wb') as f:
        p1 = Popen(args, stdout=PIPE)
        p2 = Popen('gzip', stdin=p1.stdout, stdout=f)
        p1.stdout.close()
        p2.wait()
        p1.wait()

def tar_html_folder():
    output_filename_1 = "%s.html_dir"  % f_date
    output_filename_2 = "%s.html_dir.zip"  % f_date
    dir_name = '/var/www/html'
    dst = "%s" % date
    shutil.make_archive(output_filename_1, 'zip', dir_name)
    shutil.move(output_filename_2, dst)

def main():
    archive_path = "%s" % date
    os.mkdir(archive_path, 0755)
    backup_all_databases()
    src_file = "%s.sql.gz" % f_date
    dst = "%s" % date
    shutil.move(src_file, dst)
    tar_html_folder()

if __name__ == "__main__":
    main()

# cat of the python script to see the source code.

[23:02:39] root@jasonralph:~/py_backup_site_full # cat wordpress_backup.py

#!/usr/local/bin/python

import optparse

import os

import datetime

import shutil

from subprocess import Popen, PIPE

date = datetime.datetime.now().strftime('%Y%m%d-%s')

f_date = datetime.datetime.now().strftime('%Y%m%d')

def backup_all_databases():

args = ['mysqldump', '-u', 'root', '-pPASSWORD', '--all-databases']

with open("%s.sql.gz" % f_date, 'wb') as f:

p1 = Popen(args, stdout=PIPE)

p2 = Popen('gzip', stdin=p1.stdout, stdout=f)

p1.stdout.close()

p2.wait()

p1.wait()

def tar_html_folder():

output_filename_1 = "%s.html_dir" % f_date

output_filename_2 = "%s.html_dir.zip" % f_date

dir_name = '/var/www/html'

dst = "%s" % date

shutil.make_archive(output_filename_1, 'zip', dir_name)

shutil.move(output_filename_2, dst)

def main():

archive_path = "%s" % date

os.mkdir(archive_path, 0755)

backup_all_databases()

src_file = "%s.sql.gz" % f_date

dst = "%s" % date

shutil.move(src_file, dst)

tar_html_folder()

if __name__ == "__main__":

main()

# Lets execute the script and check the contents of the directory it creates. 
[23:02:41] root@jasonralph:~/py_backup_site_full # python wordpress_backup.py
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

# Long listing of the directoy.
[23:03:47] root@jasonralph:~/py_backup_site_full # ls -l
total 8
drwxr-xr-x 2 root root 4096 May  7 23:03 20160507-1462676621
-rwxr-xr-x 1 root root 1059 May  7 23:02 wordpress_backup.py

# Change directory to the newly created folder. 
[23:03:50] root@jasonralph:~/py_backup_site_full # cd 20160507-1462676621/

# Long listing of the files in the newly created folder. 
[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l
total 79084
-rw-r--r-- 1 root root 80614105 May  7 23:03 20160507.html_dir.zip
-rw-r--r-- 1 root root   360709 May  7 23:03 20160507.sql.gz

# Lets execute the script and check the contents of the directory it creates.

[23:02:41] root@jasonralph:~/py_backup_site_full # python wordpress_backup.py

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

# Long listing of the directoy.

[23:03:47] root@jasonralph:~/py_backup_site_full # ls -l

total 8

drwxr-xr-x 2 root root 4096 May 7 23:03 20160507-1462676621

-rwxr-xr-x 1 root root 1059 May 7 23:02 wordpress_backup.py

# Change directory to the newly created folder.

[23:03:50] root@jasonralph:~/py_backup_site_full # cd 20160507-1462676621/

# Long listing of the files in the newly created folder.

[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l

total 79084

-rw-r--r-- 1 root root 80614105 May 7 23:03 20160507.html_dir.zip

-rw-r--r-- 1 root root 360709 May 7 23:03 20160507.sql.gz

So you can see we generated 2 files in a dated directory, I chose to use both zip and gunzip for compression algoritims. To view the contents you can run the normal linux commands to extract the files.

# unzipping the files using gunzip and zip
[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # unzip 20160507.html_dir.zip && gunzip 20160507.sql.gz

# Long listing of the files 
[23:08:31] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l
total 80660
-rw-r--r-- 1 root root 80614105 May  7 23:03 20160507.html_dir.zip
-rw-r--r-- 1 root root  1973075 May  7 23:03 20160507.sql
drwxr-xr-x 3 root root     4096 May  7 23:08 jasonralph

# Head command showing the first 10 lines of the sql file. 
[23:11:33] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # head -n 10 20160507.sql
-- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       5.1.73

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

# Long listing of the jasonralph folder in /var/www/html
[23:12:48] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls jasonralph/wordpress/
black_cat_blink.gif  image.jpg    MW_Line.mp4  rh.jpg               wp-admin              wp-config.php         wp-content   wp-links-opml.php  wp-mail.php      wp-settings.php   xmlrpc.php
delete_file.mvg      index.php    out.png      tux_linux_white.jpg  wp-blog-header.php    wp-config.php.bkup    wp-cron.php  wp-load.php        wp-pass.php      wp-signup.php
expolit.svg          license.txt  readme.html  wp-activate.php      wp-comments-post.php  wp-config-sample.php  wp-includes  wp-login.php       wp-register.php  wp-trackback.php

# unzipping the files using gunzip and zip

[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # unzip 20160507.html_dir.zip && gunzip 20160507.sql.gz

# Long listing of the files

[23:08:31] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l

total 80660

-rw-r--r-- 1 root root 80614105 May 7 23:03 20160507.html_dir.zip

-rw-r--r-- 1 root root 1973075 May 7 23:03 20160507.sql

drwxr-xr-x 3 root root 4096 May 7 23:08 jasonralph

# Head command showing the first 10 lines of the sql file.

[23:11:33] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # head -n 10 20160507.sql

-- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64)

-- Host: localhost Database:

-- ------------------------------------------------------

-- Server version 5.1.73

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

# Long listing of the jasonralph folder in /var/www/html

[23:12:48] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls jasonralph/wordpress/

black_cat_blink.gif image.jpg MW_Line.mp4 rh.jpg wp-admin wp-config.php wp-content wp-links-opml.php wp-mail.php wp-settings.php xmlrpc.php

delete_file.mvg index.php out.png tux_linux_white.jpg wp-blog-header.php wp-config.php.bkup wp-cron.php wp-load.php wp-pass.php wp-signup.php

expolit.svg license.txt readme.html wp-activate.php wp-comments-post.php wp-config-sample.php wp-includes wp-login.php wp-register.php wp-trackback.php

So there you have it, I can tar up the entire dated directory for easy offsite backup now of my entire site jasonralph.org. Hope this helps someone, feel free to copy the source code and change at will.

Best,
Jason

Analytic’s – With Google

March 16, 2016October 9, 2019 adminLeave a comment

Well I thought I would write up a quick post to demonstrate that even the slower kids like myself can achieve web visitors hitting their site if they put some effort behind it. I started this blog back in late 2012 and I only posted a couple of code snippets here and there. Then my coding skills developed a bit and I kept the domain alive so it started to get a bit more traffic. Anyway this really is not much traffic, but it’s neat to say that I have had my blog up and recording visits for some time now.

Google Analytics JR.org

So here it is, if you want to advertise hit me up at [email protected] 🙂

Jason R. Ralph

Linux All Day Everyday

Category: Coding Thoughts

HTTPSConnectionPool(host=’files.pythonhosted.org’, port=443): Read timed out

Capture AWS CLI Output With Timestamps On Each Line Of Output

AWS CLI Max Concurrent Requests Tuning

Mass Rename Files In Gcloud With Python Multiprocessing Parallel Gsutil

CENTOS6 Postgres pg_upgrade 9 to 11 – In Place – Link – No Copy – Limited Disk Space

SINOPIA NPM allow connections to GITHUB as well as the NPM registry

Python Generator Find Files With Wildcard

POSTGRES – Top 100 Tables In Tablespace

Python Backup WORDPRESS Site / DATABASE and HTML

Analytic’s – With Google