CONTINUAL INTEGRATION – Page 208 – A Technical I.T./DevOps Blog

How Do You Deploy JetBrains TeamCity to an AWS Windows Server 2016?

Problem scenario
You want to deploy TeamCity to an AWS instance running Windows Server 2016. How do you do this?

Solution
This is a simple deployment of TeamCity server for testing or evaluation purposes. The flavor of server will depend on the way you use TeamCity server. For a basic deployment, one processor and 1 GB of RAM is sufficient. This is far from enough CPU and memory for any "real" usage of TeamCity.

1. Install Java from this link. With Windows Server 2016 in AWS, downloading and running the file while accepting the defaults should work.

2. Install the JDK by following these two sub-steps.

a) In Internet Explorer 11 in an AWS instance of Windows Server 2016 go to Internet Options -> Security. Click on the "Internet" zone. Click "Custom level." In Security Settings, the window that pops up, scroll one-third of the way down to Downloads. "File download" should be set to "Enable." Click "OK" twice to close the windows. Then download it.

b) Download it here. You'll want the x64 bit version for Windows to download. You can accept the defaults during the installation itself.

3. Download TeamCity from this link.
4. a) Run the .exe to start the installation.
b) click "Next" to start the Setup Wizard
c) Read the License Agreement. If you can agree click "I agree" to the License Agreement
d) Choose the default location
e) Choose the default components to install, click next. You may have to wait a few minutes.
f) Choose the port you want TeamCity to run on. Then click next.
g) Click "Save."
h) Choose whichever option you want for "Specify a user account to run TeamCity Server service."
i) Choose whichever option you want for "Specify a user account to run TeamCity Agent service."
j) Keep the options checked for "Start Build Agent service" and "Start TeamCity Server service."
k) When you see the "Data Directory location on the TeamCity server machine:" change if you desire. You probably will just want to click "Proceed."
l) Leave the option for "Select the database type" alone. Click "Proceed." This make take five minutes or so.
m) Read the license agreement. If you can accept, check "Accept license agreement" and click "Continue."
n) Enter a username and password for the Administrator. Don't forget the credentials or store them somewhere safe. Then click "Create Account."
o) Click "Ok" if you get "TypeError: Unable to get property 'ms_id4' of undefined or null reference." Then you are done. If you do not receive this message, you are also done with deploying TeamCity.

How Do You Run a SQL Query to Identify the Primary Key of a Table in a PostgreSQL Database?

Problem scenario
You are using PostgreSQL and currently administering a database you inherited from a different company. You want to determine the primary key of a given table. How do you identify the primary key of a table?

Solution
Modify the query below so you replace "contint" with the name of the table you are interested in. The result of this SQL query will be the primary key of that table.

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = 'contint'::regclass
AND    i.indisprimary;

The query was adapted from this link.

How Do You Create a Primary Key in a PostgreSQL Table That Already Exists?

Problem scenario
You have a table that you would like to have a primary key. You would like to add a new column to be the primary key. How do you do this?

Solution
Here is a SQL command that is an illustration. Assuming the table's name is "contint" and the new column you want to add is "continual_col" with a value type of integer, issue this command:

ALTER TABLE ADD COLUMN continual_col int PRIMARY KEY;

This is just an example. Your primary key may have a different data type and a different name for the column. Substitute "contint" and "continual_col" accordingly. Then run the command.

How Do You Troubleshoot the Ansible Message ‘Error while linking: [Errno 2] No such file or directory … “state”: “absent”‘?

Problem scenario
You are using the file module in Ansible. But you get the error 'Error while linking: [Errno 2] No such file or directory ... "state": "absent"'.

What should you do?

Solution
Some people get this problem with different versions of Ansible 2.x. It is documented on this external page.

Rewrite your Ansible playbook so it uses a "shell" module and some "ln -s" command. Here is an example of the shell command:

ln -s /existing/directory/ /new/directory/via/softlink

# Where /existing/directory/ is the path of an existing directory and "/new/directory/via/softlink" is the symbolic link you

How Do You Solve This SQL Problem When You Receive the Message “ERROR: Syntax Error at or Near”?

Problem scenario
You are trying to update a SQL table with a SQL statement (using the "UPDATE" keyword). You keep getting "ERROR: syntax error at or near" with a reference of the value you are trying to give to a cell in a row in a SQL table. You verified you have no extra commas, apostrophes, or semicolons in your SQL UPDATE statement. You verified that the columns exist in the table and are spelled correctly in your UPDATE statement. What do you do to have your UPDATE statement work?

Solution
Verify your WHERE clause is correct. If your WHERE clause is looking for a value (e.g., where name = 'jones') in the table that matches 0 rows, then you may get this problem "ERROR: syntax error at or near." Once your WHERE clause matches the value of one or more rows, the UPDATE SQL statement should work.

How Do You Log into a Windows Server in Google Cloud Platform?

Problem scenario
You created a Windows server in GCP. How do you obtain credentials and connect to it?

Solution
1. Make sure the firewalls allow for the connection. See this posting if you need to change that.

2. In the GCP console, go to Computer Engine -> VM Instances.

3. Go to the VM you want to connect to. Under the "Connect" column find the small triangle that points downward. Click on it and go to "Set new Windows password."

4. Enter the username you want or use the default and click "Set."

5. Copy it down.

6. Open Remote Desktop. Connect to the external IP address of the Windows VM.

How Do You View Failed User Login Attempts to a PostgreSQL Instance?

Problem scenario
You are running a LAPP application on Ubuntu Linux with PostgreSQL. A user has complained that she cannot log into your Postgres database via a LAPP application. You are not sure if the attempts are even registering of if the user is entering an incorrect username. How do you look at a log to see a history of unsuccessful attempts at logging into the Postgresql database?

Solution
Examine this file: /var/log/postgresql/postgresql-x.y-main.log

(Substitute x.y with the major and minor versions of PostgreSQL you are running e.g., 9.5.)

It logs failed Postgres authentication attempts -- even if they come from a PHP web page (a web-based UI). If a role, like an OS user but for postgres, tries to log in from a web application with the wrong password, it will be logged in to /var/log/postgresql/postgresql-9.5-main.log (if you are using PostgreSQL 9.5). Please note that this log does not show successful database login attempts. It is not a complete history -- it only records failed attempts.

To check operating-system-level failed logins, check these files (the top two pertain to SSH both successful and unsuccessful while btmp pertains to other failed remote login attempts):

/var/log/secure
/var/log/auth.log
/var/log/btmp

How Do You Detect the Hard Drive Letter That Your PowerShell Script (.ps1 File) Is Saved On?

Problem scenario
You have a PowerShell script that you want to be able to detect the hard drive letter it resides on. How can PowerShell detect the hard drive letter that the .ps1 file itself is stored on?

Solution
Use this command: (get-location).drive.name

To get the drive letter to be in a variable named $dl, use this:
$dl = (get-location).drive.name

Now you can use $dl as the drive letter in the PowerShell script. Creating composite strings such as c:\temp\various\ is easy with PowerShell. Here is an example:

$lastsection = ':\temp\various\'
$compositeVar = $dl + $lastsection

# now compositeVar is the path x:\temp\various where x is the drive letter that the PowerShell script is ran from.

How Do You Solve the Problem “Cannot find a usable libpq.dll. Please launch heidisql64.r5168.exe from the directory where you have installed it”?

Problem scenario
In Windows 7 you start (or launch) HeidiSQL. You try to connect to a remote server but you get the error "Cannot find a usable libpq.dll. Please launch heidisql64.r5168.exe from the directory where you have installed it." How do you solve this problem?

Solution
Uninstall HeidiSQL and install the latest one from here. If you download the latest build, the problem may remain. You may need the installer.