BuycPanel Blog

BuycPanel Blog

Latest news and updates

5/07/2015 – Creating a PostgreSQL Database

Posted by Jamison on 07 05 2015.

It has been said more than once that a database is crucial to any type of website, whether simple or complex. There’s a lot of choices available as to which technology to go for when creating one, but PostgreSQL is one of the most notable.


ASAP_postgresql_iconA PostgreSQL database is a time-tested and industry-recognized solution that is packed with developer tools. Some of its significant features include point in time recovery, fault tolerance logging, tablespaces and Multi-versions Concurrency Control (MVCC), among others. These features have improved over the years, making PostgreSQL a popular choice among professional web developers.


One can quickly create a database on a running PostgreSQL server using the CREATE DATABASE (name) command. However, if creating for the first time, it is imperative to set up a storage disk area or cluster before moving on to create an actual database. Once initialized, the cluster will come with a default called potgres. Automatically created by the initdb command upon cluster setup, postgres is intended to be a template for all succeeding databases. A developer can connect to it to create his first “real” database.


It is worth noting that for every new database created within the cluster, the default template is technically copied. This implies that all changes made in one database will also be mirrored in subsequent databases. It is therefore recommended that the default template be left as is to avoid creating unnecessary objects mirrored in all others.


Another way to create a new PostgreSQL database is to use the creatdb executable command line. However, this requires the user privileges of using and creating a database, which can be confirmed by sending a query to the pg_user view. Once checked, a user must connect to an existing database before executing the command.


As general rules, database names should be within a thirty one character limit, and must start with an alphabet. There should be no two databases that have the same name within a cluster. Also, there is no limit to the number of databases that can be created, assuming that the allotted disk area has not been fully utilized.


If needed, removing a database can be done through the DROP DATABASE command, or through the dropdb executable command line. These commands are irreversible, and will permanently remove all tables and values in the deleted database.


How secure is a PostgreSQL database?


Generally secure, a PostgreSQL database revokes access and privileges from all others apart from the creator. Since a cluster contains a lot of confidential data, the creator automatically becomes the database administrator, and only him is granted full control of each object within it.


Nonetheless, while security is assured for directory contents, other users can also connect to the database using default client authentication, and even become a super user. To avoid these instances, it is best to set a cluster password, and ensure that default trust authentification is not utilized. These should be done before initializing the server for the first time to restrict permission on the onset.