So, I know that Redis should be a data cache that can be repopulated … but we use it to calculate deltas (what was the value last time) … so repopulating the information makes the first half hour or so of calculations rather slow as the application tries redis, gets nothing, and fails back to a database query. Then we get a backlog of data to churn through, and it would just be better if the Redis cache hadn’t gone away in the first place. And if you own both servers and the files are in the same format, you could just copy the cache db from the old server to the new one. But … when you cannot just copy the file and you would really prefer the data not disappear and need to be repopulated … there’s a script for that! This python script reads all of the data from the “old” server and populates it into the “new” server.
import redis
def migrate_data(redis_source_host, redis_source_port, redis_source_db, redis_source_password,
redis_dest_host, redis_dest_port, redis_dest_db, redis_dest_password):
# Connect to the source Redis server
source_client = redis.StrictRedis(host=redis_source_host, port=redis_source_port, db=redis_source_db, password=redis_source_password)
# Connect to the destination Redis server
dest_client = redis.StrictRedis(host=redis_dest_host, port=redis_dest_port, db=redis_dest_db, password=redis_dest_password)
# Fetch all keys from the source Redis
keys = source_client.keys('*')
for key in keys:
# Get the type of the key
key_type = source_client.type(key).decode('utf-8')
if key_type == 'string':
value = source_client.get(key)
print("Setting string value in dest")
dest_client.set(key, value)
elif key_type == 'list':
values = source_client.lrange(key, 0, -1)
print("Setting list value in dest")
dest_client.delete(key) # Ensure the list is empty before pushing
for value in values:
dest_client.rpush(key, value)
elif key_type == 'set':
values = source_client.smembers(key)
print("Setting set value in dest")
dest_client.delete(key) # Ensure the set is empty before pushing
for value in values:
dest_client.sadd(key, value)
elif key_type == 'zset':
values = source_client.zrange(key, 0, -1, withscores=True)
print("Setting zset value in dest")
dest_client.delete(key) # Ensure the zset is empty before pushing
for value, score in values:
dest_client.zadd(key, {value: score})
elif key_type == 'hash':
values = source_client.hgetall(key)
print("Setting hash value in dest")
dest_client.delete(key) # Ensure the hash is empty before pushing
dest_client.hmset(key, values)
print("Data migration completed.")
if __name__ == "__main__":
# Source Redis server details
redis_source_host = 'oldredis.example.com'
redis_source_port = 6379
redis_source_db = 0
redis_source_password = 'SourceRedisPassword'
# Destination Redis server details
redis_dest_host = 'newredis.example.com'
redis_dest_port = 6379
redis_dest_db = 0
redis_dest_password = 'DestRedisPassword'
# Migrate data
migrate_data(redis_source_host, redis_source_port, redis_source_db, redis_source_password,
redis_dest_host, redis_dest_port, redis_dest_db, redis_dest_password)
As communication between development and production platforms is limited for security and data integrity reasons, this creates a challenge when testing changes in development: we cannot access “real world” data with which to perform tests. Having a limited set of data in development means testing may not illuminate issues that occur at high volume or on a large scale.
Solution
While limiting communication between the prod and dev systems is reasonable, it would be beneficial to be able to replay production-like data within our development systems for testing purposes. While it is not cost effective to buy large network devices with thousands of interfaces for testing, the Python module snmpsim provides “canned responses” that simulate real devise on the production network. For simplicity, I have a bash script that launches the SNMP responder.
This responder will replay data stored in the directory /opt/snmp/snmpsim/data – any file ending in snmprec will be included in the response, and the filename prior to .snmprec is the community string to access the response data. E.G. public.snmprec is the data for the public community string
The response files are in the format OID|TAG|VALUE where OID is the OID number of the SNMP object, TAG is an integer defined at https://pypi.org/project/snmpsim/0.2.3/
Valid tag values and their corresponding ASN.1/SNMP types are:
ASN.1/SNMP Type
Tag Value
Integer32
2
Octet String
4
Null
5
Object Identifier
6
IP Address
64
Counter32
65
Gauge32
66
Time Ticks
67
Opaque
68
Counter65
70
And the value is the data to be returned for the OID object. As an example:
1.3.6.1.2.1.1.3.0|67|2293092270
1.3.6.1.2.1.1.3.0 is the sysUpTime, the data type is TimeTicks, and the system up time is 2293092270 hundredths of a second. Or 6375 hours, 20 minutes, and 24 seconds.
Items within the response file need to be listed in ascending order.
Generating Response Data
There are two methods for creating the data provided to an SNMP GET request. A response file can be created manually, populated with OID objects that should be included in the response as well as sample data. Alternatively, a network trace can be gathered from the production network and parsed to create the response file.
Manually Generated Response File
While you can literally type data into a response file, but it is far easier to use a script to generate sample data. /opt/snmp/snmpsim/_genData.py is an example of creating a response file for about 1,000 interfaces
from datetime import datetime
import random
iRangeMax = 1000
dictTags = {'Integer': '2', 'OctetString': '4', 'NULL': '5', 'ObjectIdentifier': '6', 'IPAddress': '64', 'Counter32': '65', 'Gauge32': '66', 'TimeTicks': '67', 'Opaque': '68','Counter64': '70'} # Valid tags per https://pypi.org/project/snmpsim/0.2.3/
today = datetime.now()
iftable_snmp_objects = [
('1.3.6.1.2.1.2.2.1.1', 'Integer', lambda i: i), # ifIndex
('1.3.6.1.2.1.2.2.1.2', 'OctetString', lambda i: f"SampleInterface{i}"), # ifDescr
('1.3.6.1.2.1.2.2.1.3', 'Integer', lambda i: 6), # ifType
('1.3.6.1.2.1.2.2.1.4', 'Integer', lambda i: 1500), # ifMtu
('1.3.6.1.2.1.2.2.1.5', 'Gauge32', lambda i: 100000000), # ifSpeed
('1.3.6.1.2.1.2.2.1.6', 'OctetString', lambda i: f"00:00:00:00:{format(i, '02x')[:2]}:{format(i, '02x')[-2:]}"), # ifPhysAddress
('1.3.6.1.2.1.2.2.1.7', 'Integer', lambda i: 1), # ifAdminStatus
('1.3.6.1.2.1.2.2.1.8', 'Integer', lambda i: 1), # ifOperStatus
('1.3.6.1.2.1.2.2.1.9', 'TimeTicks', lambda i: int((datetime.now() - datetime(2024, random.randint(1, today.month), random.randint(1, today.day))).total_seconds()) * 100), # ifLastChange
('1.3.6.1.2.1.2.2.1.10', 'Counter32', lambda i: random.randint(3, i*50000)), # ifInOctets
('1.3.6.1.2.1.2.2.1.11', 'Counter32', lambda i: random.randint(3, i*50000)), # ifInUcastPkts
('1.3.6.1.2.1.2.2.1.12', 'Counter32', lambda i: random.randint(0, 80)), # ifInNUcastPkts
('1.3.6.1.2.1.2.2.1.13', 'Counter32', lambda i: random.randint(0, 80)), # ifInDiscards
('1.3.6.1.2.1.2.2.1.14', 'Counter32', lambda i: random.randint(0, 80)), # ifInErrors
('1.3.6.1.2.1.2.2.1.15', 'Counter32', lambda i: random.randint(3, i*50000)), # ifInUnknownProtos
('1.3.6.1.2.1.2.2.1.16', 'Counter32', lambda i: random.randint(3, i*50000)), # ifOutOctets
('1.3.6.1.2.1.2.2.1.17', 'Counter32', lambda i: random.randint(3, i*50000)), # ifOutUcastPkts
('1.3.6.1.2.1.2.2.1.18', 'Counter32', lambda i: random.randint(3, i*50000)), # ifOutNUcastPkts
('1.3.6.1.2.1.2.2.1.19', 'Counter32', lambda i: random.randint(0, 80)), # ifOutDiscards
('1.3.6.1.2.1.2.2.1.20', 'Counter32', lambda i: random.randint(0, 80)), # ifOutErrors
]
ifxtable_snmp_objects = [
('1.3.6.1.2.1.31.1.1.1.1', 'OctetString', lambda i: f"SampleInterface{i}"), # ifName
('1.3.6.1.2.1.31.1.1.1.15', 'Gauge32', lambda i: "100"), # ifHighSpeed
('1.3.6.1.2.1.31.1.1.1.6', 'Counter32', lambda i: random.randint(3, i*50000)), # ifHCInOctets
('1.3.6.1.2.1.31.1.1.1.10', 'Counter32', lambda i: random.randint(3, i*60000)), # ifHCOutOctets
]
# Print IFTable data
for oid_base, tag_type, value_func in iftable_snmp_objects:
for i in range(1, iRangeMax+1):
value = value_func(i)
print(f"{oid_base}.{i}|{dictTags.get(tag_type)}|{value}")
# IP-MIB objects for managing IP addressing
# ipAdEntAddr: The IP address to which this entry's addressing information pertains
print(f"1.3.6.1.2.1.4.20.1.1|{dictTags.get('IPAddress')}|10.5.5.5")
# ipAdEntIfIndex: The index value which uniquely identifies the interface to which this entry is applicable
print(f"1.3.6.1.2.1.4.20.1.2|{dictTags.get('OctetString')}|1")
# ipAdEntNetMask: The subnet mask associated with the IP address of this entry
print(f"1.3.6.1.2.1.4.20.1.3|{dictTags.get('OctetString')}|255.255.255.0")
# hrSWRunIndex: An index uniquely identifying a row in the hrSWRun table
print(f"1.3.6.1.2.1.25.4.2.1.1.1|{dictTags.get('Integer')}|1")
# hrSWRunName: The name of the software running on this device
print(f"1.3.6.1.2.1.25.4.2.1.2.1|{dictTags.get('OctetString')}|LJRSNMPAgent")
# hrSWRunID: The product ID of the software running on this device
print(f"1.3.6.1.2.1.25.4.2.1.3.1|{dictTags.get('ObjectIdentifier')}|1.3.6.1.4.1.25709.55")
# hrSWRunPath: The path of the software running on this device
print(f"1.3.6.1.2.1.25.4.2.1.4.1|{dictTags.get('OctetString')}|/opt/snmp/snmpsim/_agent.sh")
# hrSWRunParameters: Operational parameters for the software running on this device
print(f"1.3.6.1.2.1.25.4.2.1.5.1|{dictTags.get('OctetString')}|-L")
# hrSWRunType: The type of software running (e.g., operating system, application)
print(f"1.3.6.1.2.1.25.4.2.1.6.1|{dictTags.get('Integer')}|4")
# hrSWRunStatus: The status of this software (running, runnable, notRunnable, invalid)
print(f"1.3.6.1.2.1.25.4.2.1.7.1|{dictTags.get('Integer')}|1")
for oid_base, tag_type, value_func in ifxtable_snmp_objects:
for i in range(1, iRangeMax+1):
value = value_func(i)
print(f"{oid_base}.{i}|{dictTags.get(tag_type)}|{value}")
Network Capture
Even better, parse a network capture file.
Capture Data
On the server that gathers SNMP data from the host we want to simulate, use a network capture utility to gather the SNMP communication between the server and the desired device.
tcpdump -i <interface> -w <filename>.pcap
E.G. to record the communication with 10.5.171.114
tcpdump ‘host 10.5.171.114 and (tcp port 161 or tcp port 162 or udp port 161 or udp port 162)’ -w /tmp/ar.pcap
Note – there Is no benefit to capturing more than one cycle of SNMP responses. If data is captured immediately, that means the devices were in the middle of a cycle. End the capture and start a new one shortly. There should be no packets captured for a bit, then packets during the SNMP polling cycle, and then another pause until the next cycle.
Parsing The Capture Data Into A Response File
The following script parses the capture file into an snmprec response file – note, I needed to use 2.6.0rc1 of scapy to parse SNMP data. The 2.5.0 release version failed to parse most of the packets which I believe is related to https://github.com/secdev/scapy/issues/3900
from scapy.all import rdpcap, SNMP
from scapy.layers.inet import UDP
from scapy.packet import Raw
from scapy.layers.snmp import SNMP, SNMPvarbind, SNMPresponse, SNMPbulk
from scapy.all import conf, load_layer
from scapy.utils import hexdump
from scapy.all import UDP, load_contrib
from scapy.packet import bind_layers
import os
from datetime import datetime
import argparse
# Ensure Scapy's SNMP contributions are loaded
load_contrib("snmp")
def sort_by_oid(listSNMPResponses):
"""
Sorts a list of "OID|TAG|Value" strings by the OID numerically and hierarchically.
:param listSNMPResponses: A list of "OID|TAG|Value" strings.
:return: A list of "OID|TAG|Value" strings sorted by OID.
"""
# Split each element into a tuple of (OID list, original string), converting OID to integers for proper comparison
oid_tuples = [(list(map(int, element.split('|')[0].split('.'))), element) for element in listSNMPResponses]
# Sort the list of tuples by the OID part (the list of integers)
sorted_oid_tuples = sorted(oid_tuples, key=lambda x: x[0])
# Extract the original strings from the sorted list of tuples
sorted_listSNMPResponses = [element[1] for element in sorted_oid_tuples]
return sorted_listSNMPResponses
parser = argparse.ArgumentParser(description='This script converts an SNMP packet capture into a snmpsim response file')
parser.add_argument('--filename', '-f', help='The capture file to process', required=True)
args = parser.parse_args()
strFullCaptureFilePath = args.filename
strCaptureFilePath, strCaptureFileName = os.path.split(strFullCaptureFilePath)
# Valid tags per https://pypi.org/project/snmpsim/0.2.3/
dictTags = {'ASN1_INTEGER': '2', 'ASN1_STRING': '4', 'ASN1_NULL': '5', 'ASN1_OID': '6', 'ASN1_IPADDRESS': '64', 'ASN1_COUNTER32': '65', 'ASN1_GAUGE32': '66', 'ASN1_TIME_TICKS': '67', 'Opaque': '68','ASN1_COUNTER64': '70'}
listSNMPResponses = []
listSNMPResponses.append("1.3.6.1.2.1.25.4.2.1.1.1|2|1")
listSNMPResponses.append("1.3.6.1.2.1.25.4.2.1.2.1|4|LJRSNMPAgent")
listSNMPResponses.append("1.3.6.1.2.1.25.4.2.1.3.1|6|1.3.6.1.4.1.25709.55")
listSNMPResponses.append("1.3.6.1.2.1.25.4.2.1.4.1|4|/opt/snmp/snmpsim/_agent.sh")
listSNMPResponses.append("1.3.6.1.2.1.25.4.2.1.5.1|4|-L")
listSNMPResponses.append("1.3.6.1.2.1.25.4.2.1.6.1|2|4")
listSNMPResponses.append("1.3.6.1.2.1.25.4.2.1.7.1|2|1")
i = 0
if True:
packets = rdpcap(strFullCaptureFilePath)
# Packets are zero indexed, so packet 1 in script is packet 2 in Wireshark GUI
#for i in range(0,4):
for packet in packets:
print(f"Working on packet {i}")
i = i + 1
if SNMP in packet:
snmp_layer = packet[SNMP]
if isinstance(packet[SNMP].PDU,SNMPresponse):
snmp_response = snmp_layer.getfield_and_val('PDU')[1]
if hasattr(snmp_response, 'varbindlist') and snmp_response.varbindlist is not None:
for varbind in snmp_response.varbindlist:
strOID = varbind.oid.val if hasattr(varbind.oid, 'val') else str(varbind.oid)
strValue = varbind.value.val if hasattr(varbind.value, 'val') else str(varbind.value)
strType = type(varbind.value).__name__
if dictTags.get(strType):
iType = dictTags.get(strType)
else:
iType = strType
if isinstance(strValue, bytes):
print(f"Decoding {strValue}")
strValue = strValue.decode('utf-8',errors='ignore')
print(f"OID: {strOID}, Type: {strType}, Tag: {iType}, Value: {strValue}")
listSNMPResponses.append(f"{strOID}|{iType}|{strValue}")
else:
print(f"Not a response -- type is {type(packet[SNMP].PDU)}")
elif Raw in packet:
print(f"I have a raw packet at {i}")
else:
print(dir(packet))
print(f"No SNMP or Raw in {i}: {packet}")
# Sort by OID numbers
listSortedSNMPResponses = sort_by_oid(listSNMPResponses)
f = open(f'/opt/snmp/snmpsim/data/{datetime.now().strftime("%Y%m%d")}-{strCaptureFileName.rsplit(".", 1)[0]}.deactivated', "w")
for strSNMPResponse in listSortedSNMPResponses:
print(strSNMPResponse)
f.write(strSNMPResponse)
f.write("\n")
f.close()
This will create an snmpsim response file at /opt/snmp/snmpsim/data named as the capture file prefixed with the current year, month, and date. I.E. My ar.cap file results are /opt/snmp/snmpsim/data/20240705-ar.deactivated – you can then copy the file to whatever community string you want – cp 20240705-ar.deactivated CommunityString.snmprec
################################################################################
## Install from Repo and Sign Modules
################################################################################
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
yum install kernel-devel
# Install kmod version of ZS
yum install https://zfsonlinux.org/epel/zfs-release-2-3$(rpm --eval "%{dist}").noarch.rpm
dnf config-manager --disable zfs
dnf config-manager --enable zfs-kmod
yum install zfs
# And autoload
echo zfs >/etc/modules-load.d/zfs.conf
# Use rpm -ql to list out the kernel modules that this version of ZFS uses -- 2.1.x has quite a few of them, and they each need to be signed
# Sign zfs.ko and spl.ko in current kernel
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/$(uname -r)/weak-updates/zfs/zfs/zfs.ko
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/$(uname -r)/weak-updates/zfs/spl/spl.ko
# And sign the bunch of other ko files in the n-1 kernel rev (these are symlinked from the current kernel)
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/avl/zavl.ko
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/icp/icp.ko
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/lua/zlua.ko
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/nvpair/znvpair.ko
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/unicode/zunicode.ko
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/common/zcommon.ko
/usr/src/kernels/$(uname -r)/scripts/sign-file sha256 /root/signing/MOK.priv /root/signing/MOK.der /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/zstd/zzstd.ko
# Verify they are signed now
modinfo -F signer /usr/lib/modules/$(uname -r)/weak-updates/zfs/zfs/zfs.ko
modinfo -F signer /usr/lib/modules/$(uname -r)/weak-updates/zfs/spl/spl.ko
modinfo -F signer /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/avl/zavl.ko
modinfo -F signer /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/icp/icp.ko
modinfo -F signer /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/lua/zlua.ko
modinfo -F signer /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/nvpair/znvpair.ko
modinfo -F signer /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/unicode/zunicode.ko
modinfo -F signer /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/zcommon/zcommon.ko
modinfo -F signer /lib/modules/4.18.0-513.18.1.el8_9.x86_64/extra/zfs/zstd/zzstd.ko
# Reboot
init 6
# And we've got ZFS, so create the pool
zpool create pgpool sdc
zfs create zpool/zdata
zfs set compression=lz4 zpool/zdata
zfs get compressratio zpool/zdata
zfs set mountpoint=/zpool/zdata zpool/zdata
What happens if you only sign zfs.ko? All sorts of errors that look like there’s some sort of other problem — zfs will not load. It will tell you the required key is not available
May 22 23:42:44 sandboxserver systemd-modules-load[492]: Failed to insert 'zfs': Required key not available
Using insmod to try to manually load it will tell you there are dozens of unknown symbols:
May 22 23:23:23 sandboxserver kernel: zfs: Unknown symbol ddi_strtoll (err 0)
May 22 23:23:23 sandboxserver kernel: zfs: Unknown symbol spl_vmem_alloc (err 0)
May 22 23:23:23 sandboxserver kernel: zfs: Unknown symbol taskq_empty_ent (err 0)
May 22 23:23:23 sandboxserver kernel: zfs: Unknown symbol zone_get_hostid (err 0)
May 22 23:23:23 sandboxserver kernel: zfs: Unknown symbol tsd_set (err 0)
But the real problem is that there are unsigned modules so … there are unknown symbols. But not because something is incompatible. Just because the module providing that symbol will not load.
The new servers being built at work use SecureBoot — something that you don’t even notice 99% of the time. But that 1% where you are doing something “strange” like trying to use OpenZFS … well, you’ve got to sign any kernel modules that you need to use. Just installing them doesn’t work — they won’t load.
To sign a kernel module, first you need to create a signing key and use mokutil to import it into the machine owner key store.
cd /root
mkdir signing
cd signing
openssl req -new -x509 -newkey rsa:2048 -keyout MOK.priv -outform DER -out MOK.der -nodes -days 36500 -subj "/CN=Windstream/"
mokutil --import MOK.der
When you run mokutil, you will set a password. This password will be needed to complete importing the key to the machine.
Get access to the console — out of band management, vSphere manager, stand in front of the server. Reboot, and there will be a “press any key” screen for ten seconds that begins the import process. Press any key!
Select “Enroll MOK”
View the key and verify it is the right one, then use ‘Continue’ to import it
Enter the password used when you ran mokutil
Then reboot
To verify your key has been successfully enrolled:
Our database storage is sizable. To reduce the financial impact of storing so much data, we opted to use a compressed file system. This allows us to maintain, for example, 8TB of data in under 2TB of space. Unfortunately, the ZFS file system we use to compress our data is no longer “built in” with newer version of RedHat.
There are alternatives. BTRFS is a long-standing option, however it’s got reliability issues (we piloted BTRFS on one of the read only replicas, and the compression ratio is nowhere near as good — the 2TB of ZFS data filled the 10TB BTRFS disk even using the better compression option. And I/O was so slow there was a continual replication backlog). RedHat introduced Virtual Disk Optimizer to replace ZFS. In theory, it’s better since it also deduplicates data (e.g. if every one of us saved the same PPT presentation to the disk, only one copy would actually be stored). That’s great for email and file shares where a lot of people are likely to store the same information. Not so useful on a database server where there’s little to de-duplicate. It does, however, compress data … so we decided to try it out.
The results, unfortunately, are not spectacular. VDO does not allow you to do much customization of the compression. It’s on or off. I’ve found some people tweaking it up in unsupported ways, but the impetus behind trying VDO was that it’s supported by RedHat. Making unsupported changes to it defeats that purpose. And the compression that we’re seeing is far less than we get in ZFS. Our existing servers run between 4.5x and 6x compression
In VDO, however, we don’t even get a 2x compression factor. 11TB of information is stored in 8TB of space! That’s 1.4x
So, while we found the performance of VDO to be satisfactory and it’s really easy to use in newer RedHat releases … we’d have to increase our 20TB LUNs to 80TB to continue storing the data we store today. That seems like A Really Bad Idea(tm).
Seems like I’m going to have to sort out using OpenZFS on the new servers.
I often need to quickly see if a cert is going to expire — I’ve got nice monitoring scripts too, but something that I can run from the command line right now
While Tableau doesn’t have anything nice like a ‘dumpster’ from which you can restore a deleted workbook, it does at least keep tables for historic events like workbook deletion. The following query finds records where a workbook with FOOBAR in its name was deleted. It lists all of the event info as well as info on the user who deleted it. Near as I can tell, the “created” date for the historical_events table is the date the workbook was deleted (from my restore, I know the workbook itself was created last year!)
SELECT historical_events.*, hist_workbooks.*, hist_users.*
FROM historical_events
left outer join historical_event_types on historical_event_types.type_id = historical_events.historical_event_type_id
left outer join hist_workbooks on hist_workbooks.id = historical_events.hist_workbook_id
left outer join hist_users on hist_users.id = historical_events.hist_actor_user_id
WHERE historical_event_types.name = 'Delete Workbook'
and hist_workbooks.name like '%FOOBAR%'
;
A very, very long time ago (2002-ish), we moved to using AD to store our Oracle connections — it’s far easier to edit the one connection entry in Active Directory than to distribute the latest connection file to every desktop and server in the company. Frankly, they never get to the servers. Individuals enter the connections they need … and update them when something stops working and they find the new host/port/etc. Unfortunately, Oracle used an anonymous connection to retrieve the data. So we’ve had anonymous binds enabled in Active Directory ever since. I no longer support AD, so haven’t really kept up with it … until a coworker asked why this huge security vulnerability was specifically configured for our domain. And I gave him the whole history. While we were chatting, a quick search revealed that Oracle 21c and later clients actually can use a wallet for credentials in the sqlnet.ora file:
RedHat is phasing out ZFS – there are several reasons for this move, but primarily ZFS is a closed source Solaris (now Oracle) codebase. While OpenZFS exists, it’s not quite ‘the same’. RedHat’s preferred solution is Virtual Data Optimizer (VDO). This page walks through the process of installing PostgreSQL and creating a database cluster on VDO and installing TimescaleDB extension on the database cluster for RedHat Enterprise 8 (RHEL8)
Before we create a VDO disk, we need to install it
yum installvdo kmod-kvdo
Then we need to create a vdo – here a VDO named ‘PGData’ is created on /dev/sdb – a 9TB volume on which we will hold 16TB
The main reason for using VDO with Postgres is because of its compression feature – this is automatically enabled, although we may need to tweak settings as we test it.
We now have a place in our pool where we want our Postgres database to store its data. So let’s go ahead and install PostgreSQL,
here we are using RHEL8 and installing PostgreSQL 12
Once the installation is done we need to initiate the database cluster and start the server . Since we want our Postgres to store data in our VDO volume we need to initialize it into our custom directory, we can do that in many ways,
In all cases we need to make sure that the mount point of our zpool i.e., ‘/pgpool/pgdata/’ is owned by the ‘postgres’ user which is created when we install PostgreSQL. We can do that by running the below command before running below steps for starting the postgres server
mkdir/pgpool/pgdata
chown-R postgres:postgres /pgpool
Customize the systemd service by editing the postgresql-12 unit file and updateding the PGDATA environment variable
After installing we need to add ‘timescale’ to shared_preload_libraries in our postgresql.conf, Timescale gives us ‘timescaledb-tune‘ which can be used for this and also configuring different settings for our database. Since we initialize our PG database cluster in a custom location we need to point the direction of postgresql.conf to timescaledb-tune it also requires a path to our pg_config file we can do both by following command.
After running above command we need to restart our Postgres server, we can do that by one of the below commands
systemctl restart postgresql-12
After restarting using one of the above commands connect to the database you want to use Timescale hypertables in and run below statement to load Timescale extension
CREATEEXTENSION IF NOTEXISTS timescaledb CASCADE;
you can check if Timescale is loaded by passing ‘\dx’ command to psql which will load the extension list.
in order to configure PostgreSQL to allow remote connection we need to do couple of changes as below