Configuring Postgresql on Openstack environment

Changing the password for Postgresql

1. sudo -u postgres psql
• Access the postgresql with the default user postgres with (sudo)'root' credential
2. ALTER USER postgres PASSWORD 'newpassword';
• This command will reset the password for the specified user name 'postgres'
• The same command can be used to reset any of the User who has forgotten their password

Access Postgresql from Openstack - Linux

1. psql -U postgres -h localhost
• This will connect to the default database 'postgres' which is available in the same instance as we have provided 'localhost' for '-h' after providing your password
• If incase you want to connect to another database other than your default database then you can specify the database to the end of this command as mentioned below
• psql -U postgres -h localhost NewDatabase
2. By default, we will only have postgres as the database in any new instance. To create a new database you can use the command provided above to connect to 'postgres' database. Once you get connected to database you should see the screen displaying the database name as 'postgres=#'.
• Create Database NewDatabase;
3. After the database is created you can connect to that NewDatabase and perform all other database functions.

Configuring postgres to allow accessing with the IP address

Postgresql will by default be accessed by providing localhost as the hostname. To enable connecting using the IP address, you must follow the below steps,

1.     Modify the postgresql.conf file listen addresses

To modify the postgresql.conf file:

a.     Open a Terminal window

b.    su - postgres

c.     Type cd  /usr/local/pgsql/bin

Note:  Depending on your install environment the path may vary. You can also try to check the folder path '/var/lib/pgsql/'

d.    Type  ./pg_ctl stop -D /usr/local/pgsql/data -m smart to stop PostgreSQL. 

Where the path listed after –D is the path to the data directory

If this doesnt work, you can also try the below command

            sudo systemctl stop postgresql.service

e.     Update the /usr/local/pgsql/data/postgresql.conf file. 

Example:  vi /usr/local/pgsql/data/postgresql.conf

f.      Search for the line #listen_addresses = 'local_host'

g.    Edit the value to ‘*’ this will start postmaster with the TCP/IP connectivity option.

Example:  listen_addresses = '*' 

Note:  If you are using vi as the editor press i to insert text

Note:  This has the PostgreSQL engine listening to any TCP/IP sockets. You need to setup the permission for the specific TCP/IP address you are to connect.

h.     Save the changes. 

Note:   If you are using vi as the editor press i to insert then press :wq! to save the changes.

2.     Verify  the pg_hba.conf file has authentication set to trust

This solution will verify that the authentication method used by PostgreSQL set to trust, which will allow anyone who is authorized to access the PostgreSQL server to connect to the database.

To verify the pg_hba.conf file set to allow trust authentication:

a.     Open a Terminal window

b.    su - postgres

c.     Type cd  /usr/local/pgsql/data

Note:  Depending on your install environment the path may vary

d.    Update the /usr/local/pgsql/data/pg_hba.conf file to allow TCP/IP connectivity to the database.

Example:  vi /usr/local/pgsql/data/pg_hba.conf

e.     Verify the host entry as shown below.  It should be set to trust and not md5.

host     all        all        127.0.0.1/32      trust    

 Note:  If you are not using localhost to connect to PostgreSQL  and will instead be using the hostname, you will need to add an entry for the associated IP for the hostname.  See example below.

Example :

Add the following entry to the bottom of the file

host     all        all        x.x.x.x/32          trust    

Where the x.x.x.x is the IP address of the target machine. This line allows all users on the target machine to connect to PostgreSQL. You can adjust the configuration to map to the standards within your organization.

f.     Restart PostgreSQL

a.     Cd to /usr/local/pgsql/bin

b.    Enter ./pg_ctl start -D /usr/local/pgsql/data start

Release Management Tool from Microsoft for CICD

Release Management (RM) Tool

Manage the release of your app by deploying it to a specific environment for each separate release step, and by controlling the process through approvals for each step.

RM Installation

Download Release Management Server, its client, and the deployment agent from VisualStudio.com and then install the server and client.

Important:This is a 90-day trial version of Release Management. For information about obtaining a non-trial version, see How to buy Release Management or Release Management Licensing. If you are an MSDN subscriber, you can download a non-trial version from the MSDN Subscriber website.

On computers that will make up the environments where you will deploy your release, install the deployment agent. These computers can be test, QA, or production environments.

Configure the Environment

The next step is to create the different environments to be used in the release path.

• Go to Configure Paths
• Select Environments
• Click on "New"

Fill the general information form. Now, we have to link this environment to a server (remember, a server is a machine with the deployment agent installed).

• Click on Link existing.

Select the server and click on Link. Save the environment and repeat the operation for the QA and PROD environments.

After the environments are configured, we are ready to configure the release path. A release path is the path used for distributing the software. We can define as many paths as we need (standard, customer emergency, etc).

• Go to Configure Paths.
• Release Paths.
• click on New.

After filling the name and description of the release path, we are ready to configure the stages. Click on Add. Select the stage type from the Stage dropdown. Select the environment associated to the stage from the Environment dropdown. Each step is composed of 3 sequential steps:
Acceptance step: the user selected as the approver in the Approver dropdown will be the responsible for approving or rejecting the deployment of the release in the stage. This step can be automated by selecting the Automated check box.
Deployment step is composed of 2 parts: •Deployment of the components: the user selected as the approver in the Approver dropdown will have the responsibility of the deployment and he will be notified about it.
Validation of the deployment: the user selected as the validator in the Validator dropdown will have the reponsibility to validate that the components have been deployed correctly.
Apporval step: the users added to the Approvers list will have the responsibility to aprove or reject the release.
Repeat these steps for all four stages.

Create the Release Template

Configure the release template it will shows how to create the component and deploy the application.

• Click on “ Configure Apps” tab.
• Select the “Agent based release template” Menu option.
• Click on “New” button and fill the basic information

Name: Name of the release template.
Description: Give the description of the new Release Template.
Release path Select the Release path from the dropdown.
Click on Edit button next to Build Definition textbox and select the build that the release will use.

Now fill this deployment sequence to be able to deploy MSI.to do this create the component for MSI Installation

Create the component for MSI installation

• Right click on "Components"
• Click on Add.
• Click on "New" button

In Source tab select the option which is used for your build.

In “Deployment tab” select the “MSI Deployer” Tool . Because we are deploying the application using the MSI. If you project using any other tool based upon you can choose to your tool from dropdown option.

In “Configuration variables” tab add the variables which will required for your component.

Configure the Release Template

Tags: This is environment you are going to deploy the application.
Servers: These are the servers listed out in your environment.
Components: This component is used for install the MSI.

Installer: Mention the MSI name as argument.
build definition should use the releaseTemplate.

Set the Releaseproperty "TRUE".

Release the build

• Click on “Releases” tab
• Click on “Releases” Menu option.
• Click on “New” button.

Name: Mention the Name of the release.
ReleaseTemplate: Select the Release template name from drop down.
Build: Click on “Latest” link it should automatically select the latest build number builds successfully.
After fill the all the details click on “Start” button.

It triggered the build. It shows the log file of each and every action of the deployment.

once the Status was succeeded application successful deployed into the server

XML Preprocess and its usage for configuration management

What is XML Preprocess?

XmlPreprocess is a command-line utility that can modify annotated XML files much like a code preprocessor. It is useful for deploying configuration files to different environments making substitutions such as connection strings. It is easily integrated into almost any script, build tool or deployment package to simplify and centralize your deployment strategy.

Goals

1. Single source of truth - Avoid maintaining parallel copies of configuration files, templates or transforms of configuration files for each environment. This eliminates the "getting out of sync" problem experienced when developers need to add something to configuration and forget to update it in several places.
2. Get and Go - Keep the configuration files fully operational right out of source control. Ideally, developers should be able to get the file directly from source control and run without having to touch it up for their local development environment. This is accomplished by decorating the configuration files with non-breaking XML comments that contain instructions for the preprocessor. These comments lay dormant, invisible to the configuration system, allowing reasonable development defaults to be used in the file. The comments are only used by the preprocessor at deployment-time.
3. Central Configuration Management - Provide a single, central place to manage all configuration settings for the entire system without needing to know anything about where the settings go, nor having to edit XML. The Excel spreadsheet is an easy-to-use tool for anyone, including non-developers to manage all of your configuration settings. If you don’t want to use Excel, version 2.0.15 has added new data sources such as CSV files, databases, and custom data sources where you can store and manage them however you want.

Examples

For example if you want to turn debug page compilation off when in production, you might do something like this:

The debug setting will remain true for an unprocessed file, but when this file is deployed using the XmlPreprocess tool with the "production" property defined, the ifdef condition will be tested, and if true, the comments around its body content will be removed, and the else branch will be omitted entirely. This will render the following:

Another powerful way to use the preprocessor is to substitute properties into placeholders in your XML file much like in Ant or NAnt. Properties can be defined in an external XML file or passed on the command line to XmlPreprocess.exe.

For example if you have an application setting that contains the name of a remote server, but the name of that server changes from environment to environment, you may want to mark-up your XML like this:

EntityFramework Reverse POCO Generator

Reverse engineers an existing database and generates EntityFramework Code First POCO classes, Configuration mappings and DbContext. Includes support for WCF. Works for SQL Server and SQL Server Compact 4.0

Link for further details on this Reverse POCO- https://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838

BCP utility for data importing

With UserId/Pwd
bcp pubs.dbo.authors out c: emppubauthors.bcp –n –Sstevenw –Usa –P
bcp pubs2.dbo.authors in c: emppubauthors.bcp –n –Sstevenw –Usa –P

For Trusted
bcp pubs.dbo.authors out c: emppubauthors.bcp –n –Sstevenw –T
bcp pubs2.dbo.authors in c: emppubauthors.bcp –n –Sstevenw –T

*-S - Server Name -U - User Name -P Pwd

Get the list of Stored Procs related to a table

SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%'

How to use SqlDataSource SelectCommand in the backend

This Tutorial is not for those who need to plugin the sqldatasource in the front end, but rather how to use the SqlDataSource in the backend codes.

There are tons of tutorial of how to write SqlDataSource for Gridview, DataList, Repeater and other objects in the front end (aspx file), but not many tutorials out there on how to simplify things for using SqlDataSource in the backend (aspx.cs file).

Example:

you have a gridview called “gvStudents”, and you have a sql query “SELECT * FROM Students”. you want to bind the query to the gridview but you want to do it in the backend. How do you do it?

here is how you do it.

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students”;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

5 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) Assign the ConnectionString to the SqlDataSource ConnectionString
4) Assign the SqlDataSource to the gridview DataSource
5) DataBind the gridview

I know your next question is “What about Stored Procedure? Can I do the same thing with a stored procedure?”

The answer is Yes, you can do the same thing with a stored procedure. here is how

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

Instead of 5 Simple Steps, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL stored procedure name to the Select Command
3) Set the SqlCommandType to be a StoredProcedure
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview


I bet your next answer is “What about SelectCommand Parameters? Can I Add Parameters to the SqlDataSource?”

*Updated*
Instead of writing another entry, I decided to continue with this entry and answer the question about the Parameters inside the SqlDataSource.

Yes you can add parameter in the SelectCommand, either the select command is sql query or stored procedure.
This is how you do it.

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students WHERE ID=@ID”;
SDSStudents.SelectParameters.Add(“ID”, TypeCode.Int32, “1″);
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;
gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();


If using with Stored Procedure use this,

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

SDSStudents.SelectParameters.Clear();
SDSStudents.SelectParameters.Add("Param1", strParamVal1);
SDSStudents.SelectParameters.Add("Param2", strParamVal2);

if (SDSStudents.SelectParameters["Param1"].DefaultValue == null || SDSStudents.SelectParameters["Param1"].DefaultValue == "")
SDSStudents.SelectParameters["Param1"].DefaultValue = " ";

if (SDSStudents.SelectParameters["Param2"].DefaultValue == null || SDSStudents.SelectParameters["Param2"].DefaultValue == "")
SDSStudents.SelectParameters["Param2"].DefaultValue = " ";


gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

Instead of 5 Simple Steps for the sqlquery, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) using SelectParamters.Add(“ParameterName”, DBType or TypeCode, “ParameterValue”) to add new parameter to the selectCommand
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview