Postgres Configuration

by

Configuration file locations:

Where are my postgres *.conf files?

Where is the Postgresql config file: ‘postgresql.conf’ on Windows?

  • Windows: C:\Program Files\PostgreSQL\x.x\data\postgresql.conf
  • Linux: /etc/postgresql/x.x/main/postgresql.conf

Go to bottom of .conf file, and add this line:

include postgresql.custom.conf

Then create file ‘postgresql.custom.conf’ in the same directory and place your customised configuration settings in it. Any settings set in the custom file will override those in the main config.

Navigate to pgtune and enter the required information, and pgtune will generate custom settings based upon total RAM size and intended use etc:

image

Copy the generated settings into file ‘postgresql.custom.conf’:

max_connections = 100
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 83886kB
maintenance_work_mem = 2GB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1

Restart Postgres.

Further reading on Postgres performance: http://www.craigkerstiens.com

Do you Encrypt your Remote Connections to SQL Azure Databases?

by

If you’re not encrypting connections to SQL Azure (or any remote SQL Server instance), then you probably should.

Encrypted connections to SQL Server use SSL,  and that is about as secure as you can get (currently).

[Remember: SSL protects only the connection, i.e. the data as it is transmitted ‘on the wire’ between the client and SQL Server. It says nothing about how the data is actually stored on the server].

Update: Don’t forget to also set TrustServerCertificate=false

SSMS

When you open SSMS’s ‘Connect to Server’ dialog, click the bottom right ‘Options’ button, and make sure you tick the checkbox ‘Encrypt Connection’:

image

SQLCMD

Ensure you add the -N command line option. The -N switch is used by the client to request an encrypted connection. This option is equivalent to the ADO.net option ENCRYPT = true.

e.g.

sqlcmd –N –U username –P password  –S servername –d databasename –Q “SELECT * FROM myTable”

Linked Servers

When creating a linked server to SQL Azure,  the @provstr parameter must be set to ‘Encrypt=yes;’:

-- Create the linked server:
EXEC sp_addlinkedserver
@server     = 'LocalLinkedServername',
@srvproduct = N'Any',
@provider   = 'SQLNCLI',
@datasrc    = '???.database.windows.net', -- Azure server name
@location   = '', 
@provstr    = N'Encrypt=yes;',       -- <<--  Important!
@catalog    = 'RemoteDatabaseName';  -- remote(Azure) database name
go

 

ADO.NET Connection strings

Add “ENCRYPT = true” to your connection string, or set the SqlConnectionStringBuilder property to True.

[Remember: don’t distribute passwords by sending as plaintext over the Internet, i.e. don’t email passwords! ]

Installing TensorFlow with GPU support on Windows 10

by

If you have a high end NVidia graphics card and you’re investigating data science with Keras+Tensorflow, then you obviously want Tensorflow to take advantage of your GPU (training times for deep neural networks can be 10 – 15 times faster even when compared to the latest CPUs).

Getting it all working can be tricky: I found this guide that explains the steps: Installing TensorFlow with GPU on Windows 10

Here’s another: How to run TensorFlow with GPU on Windows 10 in a Jupyter Notebook

The Zen of Python

by

I’ve recently been learning Python with the goal of using it alongside R for data science. It’s got a lot going for it as a language and the package (library) support covers just about every domain you can think of.

Many of the ‘C’ like languages seem intent on creating too much complexity for no other reason than ‘you can’, but Python takes a more pragmatic approach.

I particularly like the Zen of Python (PEP 20):

The Zen of Python, by Tim Peters

Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.
Sparse is better than dense.
Readability counts.
Special cases aren’t special enough to break the rules.
Although practicality beats purity.
Errors should never pass silently.
Unless explicitly silenced.
In the face of ambiguity, refuse the temptation to guess.
There should be one– and preferably only one –obvious way to do it.
Although that way may not be obvious at first unless you’re Dutch.
Now is better than never.
Although never is often better than *right* now.
If the implementation is hard to explain, it’s a bad idea.
If the implementation is easy to explain, it may be a good idea.
Namespaces are one honking great idea — let’s do more of those!

SQLDiagCmd Updated

by

I’ve updated SQLDiagCmd, my standalone executable for running any or all of Glenn Berry’s excellent SQL Server DMV diagnostic scripts.

As well as being able to target multiple servers and multiple databases, it now also has the option to exclude specified queries from being executed (such as those that might take some time to execute on large very databases or busy server instances).

The source code is available on GitHub and you can download the executable directly from these links:

SQLDiagCmd.zip

SQLDiagUI.zip

A recursive C# function

by

I was searching through email today looking for a LINQPad snippet that a colleague, James Miles, wrote some time ago, one which we used to generate the scripts for a production SQL Server database + transaction log point in time restore after IT had a little SAN mishap!

In doing so, I came across this gem from James: Solving Puzzles in C#: Poker Hands, which is not just a great example of writing a recursive function but of problem solving in general. [Where I used to work, we often used to have a Friday puzzle where I tried to come up with or find puzzles that wouldn’t be easy to solve by brute force.  This was one of the many times I was thwarted by James and others!]