Databases

This article will show you how to find your PostgreSQL configuration file, and change the port and host, maximum connections, temporary file and memory limits for PostgreSQL on Linux. Once you have your PostgreSQL database server up and running, you’ll probably want to tweak the configuration. The most common things you will want to change are the port and host bindings, and memory and file size limits if you want to tweak the performance of your server. Finding the PostgreSQL Configuration File The PostgreSQL configuration … Read more

This article will explain how to change a user’s password in PostgreSQL. PostgreSQL is one of the most popular database systems due to its flexibility and support for granular permissions and user roles that allow you to assign permissions to a group of users. Connect to the PostgreSQL Server as an Administrative User To change a password for a user in PostgreSQL, you must first connect with the psql client: Above, the psql command is used to connect to the PostgreSQL server on localhost, on port 5432, as the default postgres user. The default postgres user has administrative rights, … Read more

This article will show you how to set up user roles in PostgreSQL, and provide code examples. User roles are one of the best features of the PostgreSQL database system. This feature lets you group users and assign them permissions, making managing your database permissions and security much, much easier. What are PostgreSQL’s User Roles? In most database management systems, permission (such as being able to read or write to a database or table within a database) are assigned directly to user accounts in the … Read more

This article explains how to enable query logging in PostgreSQL. One of the most useful features of PostgreSQL includes is query logging, which allows you to better understand the performance of your databases and the queries you run on them. PostgreSQL Log files PostgreSQL’s log files will contain any error output from the server process, as well as status from the process. These logs will be created in the directory: In addition to this, you can log queries themselves for diagnostics and reporting. Updating the PostgreSQL Configuration … Read more

This short article will explain the difference between comments in PostgreSQL code and the COMMENT statement, and show you how to use both. It will show you how to add comments to your PostgreSQL query code, so that you can leave yourself (and others) notes to remind you what the code does, or explain any tricky queries you’ve constructed. Comments in PostgreSQL Code When writing the code that forms your PostgreSQL queries, you can leave comments in two ways. The first is to use two … Read more

This tutorial will demonstrate how to persist PostgreSQL database data when running PostgreSQL in Docker, by storing the data outside of the container. Docker lets you run PostgreSQL without having to install it directly – instead keeping it in an isolated container. This is useful if you want to run different versions of PostgreSQL for different projects, or just don’t want to (or can’t) install it on your system. The downside of this is that PostgreSQL’s data is stored inside the container – if the container … Read more

This short tutorial will show you how to completely delete a database from PostgreSQL, and provide code examples. Before you Delete a Database… There are a few things you should do before you try and delete a PostgreSQL database, especially if you’re working on a production server. First, take a backup of your PostgreSQL server – just in case you delete the wrong thing, or change your mind later. Next, make sure you’re logged in as the default postgresql admin user, or a user with permission to delete databases. And … Read more

This article will demonstrate how to run the PostgreSQL database service in a Docker container. Docker lets you run software in isolated environments that encapsulate specific configurations, software versions and dependencies separate from your main system (and each other). This makes it very useful for running multiple versions of the same program. There are many other reasons why you may wish to run PostgreSQL in a container – from ensuring consistency between systems, and running a version of PostgreSQL that is not available in your … Read more

This quick tutorial will show you how to delete/destroy/drop a table column in a PostgreSQL database. Before you Delete a Table in your Database… There are a few things you should do before you try and delete a table from your PostgreSQL database, especially if you’re working with data in production. First, take a backup of your PostgreSQL server – just in case you delete the wrong thing, or change your mind later. Next, make sure you’re logged in as the default postgresql admin user, or a user with permission … Read more

This article will show you how to drop/delete a stored function from a PostgreSQL database. In PostgreSQL, a stored function is a user-definable set of database queries that can be called by name, allowing for easy code re-use. Unlike stored procedures, stored functions require a return value, so they are usually used for calculating values for display, storage, or for further use. Before you Delete a Function from your Database… There are a few things you should do before you remove a stored function from … Read more