Bitz-N-Bitez

Postgres : Convert entire row to text with type conversion

One of the neat ways you can use postgres type conversion is to display an entire row in a format suitable for using in a subsequent insert etc.   That is comma delimited, quoted as needed between two parenthesis.

First lets document the customer table in this sample dvdrental database.

CREATE TABLE customer (

customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
store_id smallint NOT NULL,
first_name character varying(45) NOT NULL,
last_name character varying(45) NOT NULL,
email character varying(50),
address_id smallint NOT NULL,
activebool boolean DEFAULT true NOT NULL,
create_date date DEFAULT ('now'::text)::date NOT NULL,
last_update timestamp without time zone DEFAULT now(),
active integer

);

Now lets produce the rows converting the whole row to text as follows….

dvdrental=# select t::text from customer t limit 5;
t
-------------------------------------------------------------------------------------------------------
(524,1,Jared,Ely,jared.ely@sakilacustomer.org,530,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(1,1,Mary,Smith,mary.smith@sakilacustomer.org,5,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(2,1,Patricia,Johnson,patricia.johnson@sakilacustomer.org,6,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(3,1,Linda,Williams,linda.williams@sakilacustomer.org,7,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(4,2,Barbara,Jones,barbara.jones@sakilacustomer.org,8,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(5 rows)

dvdrental=#

While I don’t have an immediate need for this conversion, its still a fascinating little postgres trick.

SQLServer on Ubuntu Restore Tests

Just for fun I decided to test how portable backup files made on the Windows version of SQLServer restore into the Linux version of SQLServer.  I used the wizard built in to my SQL2016 SSMS software connected to the Linux version running on Ubuntu 16 on Amazon EC to run the restore.  The restore was uneventful.  I chose the AdventureWorks2012 database simply because its what I found lying around in the directory I first looked in on my laptop.

The script version of what the wizard generated is as follows…..


USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'C:\home\ubuntu\AdventureWorks2012-Full Database Backup.bak' WITH FILE = 1, MOVE N'AdventureWorks2012_Data' TO N'C:\var\opt\mssql\data\AdventureWorks2012_Data.mdf', MOVE N'AdventureWorks2012_Log' TO N'C:\var\opt\mssql\data\AdventureWorks2012_log.ldf', NOUNLOAD, STATS = 5

GO

 

Interestingly the tool browsed the directory structure ok but for some reason could not find my backup file, even when saying “all files” and no it wasn’t perms or anything.  I manually typed in the file name to the wizard browser and it worked fine.   I am NOT using the recommended version of SSMS for connecting to Linux on purpose.   I want to find out what breaks if I don’t….🙂

 

SQLServer Running On Ubuntu

So its official.  SQLServer does in fact install on linux and at first pass works.   I just spun up a Ubuntu 16 instance on Amazon EC.   I followed the install steps published by Microsoft and … well … it worked.   See transcript below.

One variance is that rather than sudo and run a command in a single step I sudo’d first then ran the step.  I only run admin tasks when I’m root, not user tasks, this is just how I roll.

Otherwise what follows is boiler plate from Microsoft.

First they setup the repository


root@ip-172-30-0-180:/etc# curl https://packages.microsoft.com/keys/microsoft.as c | apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 983 100 983 0 0 1858 0 --:--:-- --:--:-- --:--:-- 1858
OK
root@ip-172-30-0-180:/etc# curl https://packages.microsoft.com/config/ubuntu/16. 04/mssql-server.list | tee /etc/apt/sources.list.d/mssql-server.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 87 100 87 0 0 368 0 --:--:-- --:--:-- --:--:-- 370
deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial main

Then update apt-get so it knows about the new repository….

root@ip-172-30-0-180:/etc# apt-get update
Hit:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial InRelease
Get:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates InRelease [1 02 kB]
Get:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports InRelease [102 kB]
Get:4 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main Sources [868 kB ]
Get:5 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/restricted Sources [ 4,808 B]
Get:6 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/universe Sources [7, 728 kB]
Get:7 http://security.ubuntu.com/ubuntu xenial-security InRelease [102 kB]
Get:8 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/multiverse Sources [ 179 kB]
Get:9 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main Sources [206 kB]
Get:10 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/restricted Sources [1,804 B]
Get:11 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/universe So urces [110 kB]
Get:12 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/multiverse Sources [3,648 B]
Get:13 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 Packages [426 kB]
Get:14 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main Transl ation-en [164 kB]
Get:15 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/restricted amd64 Packages [6,576 B]
Get:16 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/restricted Translation-en [2,016 B]
Get:17 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/universe am d64 Packages [365 kB]
Get:18 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial InRelease [2,828 B]
Get:19 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/universe Tr anslation-en [132 kB]
Get:20 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/multiverse amd64 Packages [7,384 B]
Get:21 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/multiverse Translation-en [2,988 B]
Get:22 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/main Sour ces [3,180 B]
Get:23 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/universe Sources [1,868 B]
Get:24 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/main amd6 4 Packages [4,392 B]
Get:25 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/main Tran slation-en [3,104 B]
Get:26 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/universe amd64 Packages [2,412 B]
Get:27 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/universe Translation-en [1,216 B]
Get:28 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd6 4 Packages [940 B]
Get:29 http://security.ubuntu.com/ubuntu xenial-security/main Sources [49.1 kB]
Get:30 http://security.ubuntu.com/ubuntu xenial-security/restricted Sources [1,8 04 B]
Get:31 http://security.ubuntu.com/ubuntu xenial-security/universe Sources [14.4 kB]
Get:32 http://security.ubuntu.com/ubuntu xenial-security/multiverse Sources [728 B]
Get:33 http://security.ubuntu.com/ubuntu xenial-security/main amd64 Packages [17 7 kB]
Get:34 http://security.ubuntu.com/ubuntu xenial-security/main Translation-en [71 .2 kB]
Get:35 http://security.ubuntu.com/ubuntu xenial-security/restricted amd64 Packag es [6,576 B]
Get:36 http://security.ubuntu.com/ubuntu xenial-security/restricted Translation- en [2,016 B]
Get:37 http://security.ubuntu.com/ubuntu xenial-security/universe amd64 Packages [62.6 kB]
Get:38 http://security.ubuntu.com/ubuntu xenial-security/universe Translation-en [33.9 kB]
Get:39 http://security.ubuntu.com/ubuntu xenial-security/multiverse amd64 Packag es [2,764 B]
Get:40 http://security.ubuntu.com/ubuntu xenial-security/multiverse Translation- en [1,124 B]
Fetched 11.0 MB in 1s (6,270 kB/s)
Reading package lists... Done

Next we install the package for SQLServer from Microsoft…..

root@ip-172-30-0-180:/etc# apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
gcc-5-base gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6
libc6-dbg libcc1-0 libcurl3 libjemalloc1 libpython-stdlib
libpython2.7-minimal libpython2.7-stdlib libstdc++6 libunwind8 python
python-minimal python2.7 python2.7-minimal
Suggested packages:
gdb-doc clang glibc-doc python-doc python-tk python2.7-doc binutils
binfmt-support
The following NEW packages will be installed:
gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6-dbg libcc1-0
libcurl3 libjemalloc1 libpython-stdlib libpython2.7-minimal
libpython2.7-stdlib libunwind8 mssql-server python python-minimal python2.7
python2.7-minimal
The following packages will be upgraded:
gcc-5-base libc6 libstdc++6
3 upgraded, 18 newly installed, 0 to remove and 59 not upgraded.
Need to get 158 MB of archives.
After this operation, 734 MB of additional disk space will be used.
Get:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 l ibpython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [339 kB]
Get:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 p ython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [1,295 kB]
Get:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 python-mi nimal amd64 2.7.11-1 [28.2 kB]
Get:4 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 l ibc6 amd64 2.23-0ubuntu4 [2,586 kB]
Get:5 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 l ibpython2.7-stdlib amd64 2.7.12-1ubuntu0~16.04.1 [1,884 kB]
Get:6 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 p ython2.7 amd64 2.7.12-1ubuntu0~16.04.1 [224 kB]
Get:7 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libpython -stdlib amd64 2.7.11-1 [7,656 B]
Get:8 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 python am d64 2.7.11-1 [137 kB]
Get:9 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 g cc-5-base amd64 5.4.0-6ubuntu1~16.04.4 [16.8 kB]
Get:10 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libstdc++6 amd64 5.4.0-6ubuntu1~16.04.4 [393 kB]
Get:11 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libbabel trace1 amd64 1.3.2-1 [34.7 kB]
Get:12 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libbabel trace-ctf1 amd64 1.3.2-1 [88.3 kB]
Get:13 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 gdb amd64 7.11.1-0ubuntu1~16.04 [2,525 kB]
Get:14 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 gdbserver amd64 7.11.1-0ubuntu1~16.04 [172 kB]
Get:15 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libcc1-0 amd64 5.4.0-6ubuntu1~16.04.4 [38.8 kB]
Get:16 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libcurl3 amd64 7.47.0-1ubuntu2.2 [186 kB]
Get:17 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libunwin d8 amd64 1.1-4.1 [46.5 kB]
Get:18 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/universe amd64 libc ++1 amd64 3.7.0-1 [226 kB]
Get:19 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libc6-dbg amd64 2.23-0ubuntu4 [3,679 kB]
Get:20 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/universe amd64 libj emalloc1 amd64 3.6.0-9ubuntu1 [78.9 kB]
Get:21 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd6 4 mssql-server amd64 14.0.1.246-6 [144 MB]
Fetched 158 MB in 3s (44.7 MB/s)
Preconfiguring packages ...
Selecting previously unselected package libpython2.7-minimal:amd64.
(Reading database ... 53664 files and directories currently installed.)
Preparing to unpack .../libpython2.7-minimal_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python2.7-minimal.
Preparing to unpack .../python2.7-minimal_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking python2.7-minimal (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python-minimal.
Preparing to unpack .../python-minimal_2.7.11-1_amd64.deb ...
Unpacking python-minimal (2.7.11-1) ...
Preparing to unpack .../libc6_2.23-0ubuntu4_amd64.deb ...
Unpacking libc6:amd64 (2.23-0ubuntu4) over (2.23-0ubuntu3) ...
Setting up libc6:amd64 (2.23-0ubuntu4) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Selecting previously unselected package libpython2.7-stdlib:amd64.
(Reading database ... 53901 files and directories currently installed.)
Preparing to unpack .../libpython2.7-stdlib_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python2.7.
Preparing to unpack .../python2.7_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking python2.7 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package libpython-stdlib:amd64.
Preparing to unpack .../libpython-stdlib_2.7.11-1_amd64.deb ...
Unpacking libpython-stdlib:amd64 (2.7.11-1) ...
Processing triggers for mime-support (3.59ubuntu1) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Setting up python2.7-minimal (2.7.12-1ubuntu0~16.04.1) ...
Linking and byte-compiling packages for runtime python2.7...
Setting up python-minimal (2.7.11-1) ...
Selecting previously unselected package python.
(Reading database ... 54410 files and directories currently installed.)
Preparing to unpack .../python_2.7.11-1_amd64.deb ...
Unpacking python (2.7.11-1) ...
Preparing to unpack .../gcc-5-base_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.4) over (5.4.0-6ubuntu1~16.04.2) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.4) ...
(Reading database ... 54465 files and directories currently installed.)
Preparing to unpack .../libstdc++6_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.4) over (5.4.0-6ubuntu1~16.04.2) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Setting up libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Selecting previously unselected package libbabeltrace1:amd64.
(Reading database ... 54465 files and directories currently installed.)
Preparing to unpack .../libbabeltrace1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace1:amd64 (1.3.2-1) ...
Selecting previously unselected package libbabeltrace-ctf1:amd64.
Preparing to unpack .../libbabeltrace-ctf1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Selecting previously unselected package gdb.
Preparing to unpack .../gdb_7.11.1-0ubuntu1~16.04_amd64.deb ...
Unpacking gdb (7.11.1-0ubuntu1~16.04) ...
Selecting previously unselected package gdbserver.
Preparing to unpack .../gdbserver_7.11.1-0ubuntu1~16.04_amd64.deb ...
Unpacking gdbserver (7.11.1-0ubuntu1~16.04) ...
Selecting previously unselected package libcc1-0:amd64.
Preparing to unpack .../libcc1-0_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Selecting previously unselected package libcurl3:amd64.
Preparing to unpack .../libcurl3_7.47.0-1ubuntu2.2_amd64.deb ...
Unpacking libcurl3:amd64 (7.47.0-1ubuntu2.2) ...
Selecting previously unselected package libunwind8.
Preparing to unpack .../libunwind8_1.1-4.1_amd64.deb ...
Unpacking libunwind8 (1.1-4.1) ...
Selecting previously unselected package libc++1:amd64.
Preparing to unpack .../libc++1_3.7.0-1_amd64.deb ...
Unpacking libc++1:amd64 (3.7.0-1) ...
Selecting previously unselected package libc6-dbg:amd64.
Preparing to unpack .../libc6-dbg_2.23-0ubuntu4_amd64.deb ...
Unpacking libc6-dbg:amd64 (2.23-0ubuntu4) ...
Selecting previously unselected package libjemalloc1.
Preparing to unpack .../libjemalloc1_3.6.0-9ubuntu1_amd64.deb ...
Unpacking libjemalloc1 (3.6.0-9ubuntu1) ...
Selecting previously unselected package mssql-server.
Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
Unpacking mssql-server (14.0.1.246-6) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Setting up python2.7 (2.7.12-1ubuntu0~16.04.1) ...
Setting up libpython-stdlib:amd64 (2.7.11-1) ...
Setting up python (2.7.11-1) ...
Setting up libbabeltrace1:amd64 (1.3.2-1) ...
Setting up libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Setting up gdb (7.11.1-0ubuntu1~16.04) ...
Setting up gdbserver (7.11.1-0ubuntu1~16.04) ...
Setting up libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Setting up libcurl3:amd64 (7.47.0-1ubuntu2.2) ...
Setting up libunwind8 (1.1-4.1) ...
Setting up libc++1:amd64 (3.7.0-1) ...
Setting up libc6-dbg:amd64 (2.23-0ubuntu4) ...
Setting up libjemalloc1 (3.6.0-9ubuntu1) ...
Setting up mssql-server (14.0.1.246-6) ...

+-------------------------------------------------------------------+
| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
| Microsoft(R) SQL Server(R). |
+-------------------------------------------------------------------+

Processing triggers for libc-bin (2.23-0ubuntu3) ...
root@ip-172-30-0-180:/etc#

Next we do a brief setup…. ( ignore my host name resolution failure thats not a problem with microsoft. )


root@ip-172-30-0-180:/tmp# /opt/mssql/bin/sqlservr-setup
Microsoft(R) SQL Server(R) Setup

You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended
mode.

The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388 and found
in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? If so, please type "YES": YES

Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

Setting system administrator (SA) account password...
sudo: unable to resolve host ip-172-30-0-180

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /lib/systemd/system/mssql-server-telemetry.service.

Setup completed successfully.

Now lets validate its configured and running via systemctl ….

root@ip-172-30-0-180:/tmp# systemctl status mssql-server
● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabl
Active: active (running) since Mon 2016-11-28 16:43:37 UTC; 23s ago
Main PID: 3888 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─3888 /opt/mssql/bin/sqlservr
└─3902 /opt/mssql/bin/sqlservr

Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [84B blob data]
Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [122B blob data]
Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [145B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [66B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [75B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [96B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [100B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [71B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [124B blob data]
Nov 28 16:43:44 ip-172-30-0-180 sqlservr[3888]: [315B blob data]
root@ip-172-30-0-180:/tmp#

Here is what your processes look like initially under the new mssql user the install created.

root@ip-172-30-0-180:/var/opt/mssql/data# ps -fu mssql
UID PID PPID C STIME TTY TIME CMD
mssql 3888 1 0 16:43 ? 00:00:00 /opt/mssql/bin/sqlservr
mssql 3894 1 0 16:43 ? 00:00:00 /opt/mssql/bin/sqlservr-telemetry /var/opt
mssql 3902 3888 1 16:43 ? 00:01:57 /opt/mssql/bin/sqlservr

The default directory for files is apparently here


root@ip-172-30-0-180:/var/opt/mssql/data# ls -l /var/opt/mssql/data/
total 69696
-rw-r----- 1 mssql mssql 4194304 Nov 28 16:48 master.mdf
-rw-r----- 1 mssql mssql 2097152 Nov 28 16:55 mastlog.ldf
-rw-r----- 1 mssql mssql 8388608 Nov 28 16:53 modellog.ldf
-rw-r----- 1 mssql mssql 8388608 Nov 28 16:53 model.mdf
-rw-r----- 1 mssql mssql 15400960 Nov 28 16:55 msdbdata.mdf
-rw-r----- 1 mssql mssql 786432 Nov 28 16:55 msdblog.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 18:59 tempdb.mdf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 19:00 templog.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 16:57 TESTDB_log.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 16:53 TESTDB.mdf
root@ip-172-30-0-180:/var/opt/mssql/data#

And the pathing to files is listed as follows inside SSMS when scripting a database create statement.   Notice that it still refers to drive C: but then uses the actual linux filesystem paths.  The C: is therefore meaningless in this context.  More research on what it does with drive letters is needed.


CREATE DATABASE [TESTDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TESTDB', FILENAME = N'C:\var\opt\mssql\data\TESTDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TESTDB_log', FILENAME = N'C:\var\opt\mssql\data\TESTDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Also surprising is the following output for ipcs which does not show any shared memory segments etc owned by the new user mssql.   This is an area to follow up with research.


root@ip-172-30-0-180:/var/opt/mssql/data# ipcs -a

------ Message Queues --------
key msqid owner perms used-bytes messages

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 root 644 80 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 280 2

------ Semaphore Arrays --------
key semid owner perms nsems
0x000000a7 0 root 600 1

root@ip-172-30-0-180:/var/opt/mssql/data#

 

Automatic Startup of Postgres 9.4 on SUSE 12

For whatever reason after installing postgres9.4 via zypper on a SUSE 12 instance I had just spun up in the amazon EC cloud postgres would not automatically start at boot time.

The service file was in fact installed


/etc/systemd/system # ls -l /usr/lib/systemd/system/postgresql.service
-r--r--r-- 1 root root 413 Jun 23 22:11 /usr/lib/systemd/system/postgresql.service
/etc/systemd/system #

The contents were


[Unit]

Description=PostgreSQL database server
After=syslog.target
After=network.target

[Service]
Type=forking
User=postgres
EnvironmentFile=-/etc/sysconfig/postgresql
ExecStart=/usr/lib/postgresql-init start
ExecStop=/usr/lib/postgresql-init stop
ExecReload=/usr/lib/postgresql-init reload

# The server might be slow to stop, and that's fine. Don't kill it
SendSIGKILL=no

[Install]
WantedBy=multi-user.target

So the first thing you need to do is enable the service as follows

/etc/systemd/system # systemctl enable postgresql.service

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
/etc/systemd/system #

Then you need to validate the service starts and stops using :


service postgresql start

service postgresql status

service postgresql stop

After these few steps it now starts like a champ.

Good luck.

ABLE 529s vs 3rd Party Supplemental Needs Trusts

One of the most exciting things the Obama administration has accomplished with respect to caring for those with disabilities has been the creation of ABLE 529 accounts.   At first glance these accounts appear very much like a 3rd Party Supplemental Needs Trust.

The advantage to a ABLE 529 over a 3rd Party  Supplemental Needs Trust are cost, confidence, and ease of administration.    Setting up a 3rd Party Supplemental Needs Trust usually requires attorney time for drafting of the trust.   Then you have the open question of “will this trust be approved” when it is submitted for SSI and Medicaid etc., often there is a back and forth period between the attorney and the government, sometimes with the need to revise the trust etc.  Finally you need a trustee, corporate trustees are expensive, private family may lack the investing acumen and also fail to keep proper records etc. and jeopardize the trust by causing it to be declared a countable resource.   The ABLE 529 at first glance is a Supplemental Needs Trust in a can.   Fill out a few forms, submit some proof of disability etc. and voila you have a Supplemental Needs Trust with no need to have it drafted and absolute certainty it will be approved.

The single biggest difference with a 3rd Party Supplemental Needs Trust and ABLE 529 is that with the ABLE 529 the money is owned by the beneficiary, it is available to the beneficiary ( while not being a countable resource for benefits).   The beneficiary has the ability to withdraw the funds as they see fit, spend it as they see fit, and presumably keep the necessary records to document that their expenditures were appropriate.   On the one hand this gives the beneficiary a level of autonomy that in fact may be the goal in planning for a disabled person in the hope of their having a life that is as normal as possible.  On the other hand, depending on the nature of the disability, the nature of the person in general, etc. this may not be desirable.   A Trustee managing the trust may be better suited to ensuring the funds are not spent to soon and depleted before the life span of the beneficiary was completed.    The Trustee may be better at keeping the paperwork up to date to ensure that all expenditures meet the requirements of the various government agencies.   This can be particularly important for those with mental or behavioral disabilities, but can also be important for many people with only physical disability who, like many non disabled, simply cannot manage money for the long term and instead have spendthrift tendencies.

There are many people who are not under a guardianship but who need a gatekeeper to manage their money.   For example Social Security “Representative Payee” to manage their benefits.  The Supplemental Needs Trust manages this indirectly because of the requirement they never giving the beneficiary money directly.  The combination of Representative Payee and Supplemental Needs trust works in cases of fiscal irresponsibility mainly because the beneficiary never gets their hands on the money.   It is unclear how this would be achievable with a ABLE 529 at this time.  Its a question I’d like to see clarified.

A huge open question with ABLE 529s regards housing and how they will interact with HUD Section 8 subsidies.  Special Needs Trusts are having serious challenges threading the needle between the differing program requirement of HUD and SSI/Medicaid.   Of note ABLE 529 expenditures for housing will not reduce SSI/Medicaid, at least the way I read it but this should be verified.   SNT expenditures would reduce SSI under the In Kind Support and Maintenance ( ISM ) rules.   It may be that HUD will treat ABLE more favorably because it is a government program, but that remains to be seen.   Currently for non HUD Section 8 recipients I think housing money should be routed into the ABLE account and then back out to pay for rent without reducing SSI.

The other issue that limits the ABLE account is the $14,000 annual contribution limit, and to a lesser extent the limit on total assets in the account.  A beneficiary who will be left a significant amount of money will require a means to manage periodic distributions into the ABLE account.  The natural mechanism for that is a separate trust that exists along with the ABLE account.   Thus the ABLE account will not replace a 3rd Party Supplemental Needs Trust in the near term but instead will be a valuable tool complementing a Supplemental Needs Trust.  The able account can help to provide a channel for routing funds to work around ISM issues.   And issues around finding a competent trustee and reducing costs of establishing a 3rd Party Supplemental Needs Trust may be handled by using a competent Pooled Trust solution.

Fed or White House

The surprise election of Donald Trump to the White House has produced another surprise.  Sharply rising interest rates.  What was for 10 year treasuries an interest rate of 1.783 on Monday is on Friday 2.113 % as I type this article.

The FED has made no announcements, this interest rate swing is purely based on the speculative anticipation of a Trump presidency and the policies he will introduce.   This is significant as for the last several years the specter has existed that the FED primarily controlled the economy and did so purely with the interest rate lever and the QE purchase lever.

Whether Trumps policies will be good for the nation or not clearly remains to be seen.   Rising interest rates bode well for those looking to get income out of a portfolio, assuming its not purely rising rates from rising inflation.   Borrowers clearly will have a harder time of it.   All in though we need to get rates up off the floor, and in many cases up from under the floor, and back to a normal range.

Thoughts On November

governor_john_kasich2