Skip to main content

Setting up a Postgresql database



I am working on a new learning project to setup a simple Spring java rest api application. As part of this project, the first step was to setup a database backend for my service. For this I am going to use a PostgreSQL database on my laptop. As I do this, I am going to capture all the steps for anyone who may want to do similar things.

My laptop's OS is Xubuntu. So you can use these steps to install the database in Ubuntu or such Linux distributions. Installing in Windows is very similar and differs only in places such as the downloadable to use, the paths used during the installation etc.


  • in linux, you can install most softwares using the OS's package manager. For example, in Ubuntu, you could do: sudo apt-get install postgresql postgresql-contrib . Unfortunately my experience has been that these package managers have old versions of software. So I prefer installing from the distribution website sometimes. 
  • First download the postresql installable (appropriate one for your OS) from EnterpriseDB's website. Mine being a linux 64 bit system, i downloaded the below one.

  • This .run file cannot be executed immediately. You will need to first set its executable permission to allow for the execution (this step is specific to unix systems)

  • Now you can double click the executable to get the installation popup

  • Click Next to proceed. There will be a series of inputs that you will provide like folder to install postgreSQL, folder for data, password for the superuser(postgres), port number etc.



  • At this point, the installation will start. I choose to stick with the default values provided by postgres
  • Post the installation, update your environment variable PATH to include the postgreSQL installation's BIN folder. Below is an update I am adding in my .profile file in the linux OS for this -

  • Thats it, you are done with the installation. Now lets bring up the pgAdmin III tool to connect to the database server and create a database. A default database will be created by the installation. I will be creating another one for my application. Bring up the pgAdmin tool and connect to the database server -

  • You will need the superuser password to connect to the database server. Now you can create a new database. I decided to create a new login role as well so I dont use my superuser login everywhere.
  • For creating a new database, you can right click and choose New Database. This will bring up the wizard to guide you through the process. Thats it, its pretty simple.


        The final result will be - 


  • From here, let me show a couple of other useful things you will need (if you love using the terminal like I do). First to start the postgreSQL database server from the terminal, just use below command - sudo service postgres start

  • psql is the tool provided by postgres to allow you to connect to the database from the terminal. You can use psql from the terminal by this command - psql DATABASE_NAME USER_NAME


  • Once connected, you can query the database as needed - 


Thats it folks for this post. A simple first step towards the actual implementation of my api project. Thanks!
Labels:

Comments

Post a Comment

Popular posts from this blog

Workaround to bypass Salesforce SSO

One of the best practices for implementing Single Sign On for your Salesforce org is always ensure there is a way your System administrator can login via the standard login page side-stepping the SSO configuration.  Reason for this is if ever something goes wrong with your Idp provider's service and the SSO authentication responses are not coming as expected, all your users are unable to login. That is, if you have setup your My domain to prevent logins via standard Salesforce login urls (login.salesforce.com). This includes the System administrator as well. Only if your system administrator can somehow login, then he or she can disable the SSO settings for your domain and allow login via the normal login page as a temporary measure. What do you do in such a situation? Well Salesforce has built a workaround for this which is not well documented anywhere (probably for a good reason :) ). I found out about it from a colleague at work. If your my domain url is - https://Com
43 comments
Read more

Salesforce Big Objects - Key learnings

I remember reading about Salesforce Big Objects before they became available and thought maybe it is an option to backup data present in regular objects. That is, instead of taking a backup via an ETL tool or data loader, maybe this will be an option to backup the data in the Force.com platform itself. Now that it is GA and I am reading more about it, i think the use cases for this are more varied. Some use cases I can think of are –  Archival of key data objects within the platform: You may want to use this option if you dont use any other means to backup key data. Also this may be an attractive option for non-large enterprise customers who dont themselves invest on large data warehouses or data lakes in their enterprise architecture. Ex: customer history (if present in tasks and activities) will become huge over years but this is useful information for reporting & customer analysis. Store key information which is large volume in quantity and also high volume in transa
1 comment
Read more

DBAmp for Salesforce - salesforce integration for SQL Server DBAs

Recently i got the opportunity to explore a tool called DBAmp for integration with Salesforce. I found it to be a very useful tool which will help with any data manipulation requirements in Salesforce. Following are my learnings from the exercise. Hope it helps any of you who may need to work with this tool -  DBAmp is a SQL Server package that can be used to integrate with Salesforce. The site where this software is available is - http://www.forceamp.com/ Overview: It essentially installs on top of an existing SQL Server database and provides an OLE DB connector that can be used to connect to Salesforce. Behind the scenes, it executes API calls against Salesforce for any SQL server command it receives. Thus we can create a connection in SQL server to Salesforce org and pull data into tables in the database just as if we are querying against Salesforce tables directly. Use cases for DBAmap + Salesforce: Many use cases exist for using this tool against Salesforce. Pr
3 comments
Read more