Upgrading PostgreSQL Version
PostgreSQL
16.06.2025
Reading time: 8 min
Share
In PostgreSQL, version upgrading is performed in different ways:
- Installing packages with a new version of PostgreSQL. It is suitable only for minor updates. Before performing the update, study the release notes;
-
Using the standard
pg_dumpallprogram. It is a reliable method, but there may be a long downtime. -
Using the standard
pg_upgradeprogram. Another quick way to upgrade, but errors may occur. -
Updating via logical replication. This option has minimal downtime but is only suitable for PostgreSQL versions greater than 10.0. Earlier versions require extensions.
The choice depends on which release is used on the server and which version you want to upgrade to.
In this article, we will look at all the above upgrade methods. If you have some troubles while using PostgreSQL, check our instruction on how to set up your database.
Important notes before upgrading
The main thing is to understand the peculiarities of updates between different versions.
The numbers consist of two digits, for example, 10.1. The first digit is the major version number (10). The second digit is the minor release number (1).
Before PostgreSQL 10, the version numbers consisted of three digits. For example, in 9.6.3, 9.6 is the major release number and 3 is the minor version number.
You must understand this difference to choose the right upgrade method.
In minor versions, the data storage format does not change, so there are no compatibility problems. Therefore, the transition from PostgreSQL 10.1 to 10.6 can be carried very easily. To upgrade, turn off the server, replace the executable files, and start the server again.
However, the documentation notes that some releases may require manual changes. Therefore, always read the release notes before upgrading.
In major versions, the data format may change. This makes updating more difficult. You need to either unload the data and upload it again, use the pg_upgrade program, or use logical replication.
We'll talk about all these methods below.
Upgrading within one major version
Let's look at how to update Postgresql Version 14.1 to PostgreSQL 14.3 on an Ubuntu server.
PostgreSQL is available on Ubuntu by default. Start with the command:
sudo apt-get upgradeDebian and Ubuntu only release one version of PostgreSQL per OS release. For example, Debian Squeeze/6.0 only has PostgreSQL 8.4. If you need a different version of PostgreSQL, use packages from PGDG.
If you want the latest version of PostgreSQL, you must first install the Postgres repository.
Add a repository:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'Import the signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -Update the list of packages in the system:
sudo apt-get updateInstall the required Postgres version:
sudo apt-get -y install postgresql-14.3To view the list of installed DBMSs, run:
dpkg --get-selections | grep postgresAlso see the list of clusters:
pg_lsclustersStop PostgreSQL before making changes:
sudo service postgresql stopWhen Postgres packages are installed, they create a default cluster for you. You should rename the new Postgres cluster so there are no conflicts with the old cluster name when upgrading.
sudo pg_renamecluster 14.3 main main_pristineUpgrade the old cluster:
sudo pg_upgradecluster 14.1 mainStart the service:
sudo service postgresql startCheck the list of clusters again and make sure the new one is working:
pg_lsclustersGet rid of the old cluster:
sudo pg_dropcluster 14.1 mainUpgrading via pg_dumpall
If you need to change the major version, use the pg_dumpall program. The essence of this method is to unload data from one main version and then load it into another.
- Before unloading data, make sure that no changes are being made to the database right now. Otherwise, some of the changes may not be included in the final dump.
- Get the dump and write it to a file:
pg_dumpall > output_file- Stop the server:
pg_ctl stop- Change the name of the old directory to eliminate name conflicts:
mv /usr/local/pgsql /usr/local/pgsql.oldThe old directory can simply be deleted. But it would be wiser to rename it to leave room for restoring data. Instead of /usr/local/pgsql, specify the path to your directory.
- Install the new version from source. There is a detailed guide in the documentation.
- Form a new cluster:
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data- Transfer all changes to the
pg_hba.confandpostgresql.conffiles. - Start the database server:
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data- Restore data from backup:
/usr/local/pgsql/bin/psql -d postgres -f output_fileThe disadvantage of this method is that the server will be turned off for a long time. To reduce downtime, you can install the new server in a different directory and then run the old and new servers on different ports. To transfer data, use the command:
pg_dumpall -p 5432 | psql -d postgres -p 5433Instead of 5432 and 5433, specify the port numbers on which you ran the servers.
Upgrading via pg_upgrade
To avoid unloading and uploading data, use the pg_upgrade program. It helps you update faster. The pg_upgrade program creates system tables anew, taking into account changes in the PostgreSQL latest versions. In this case, the old data files are retained. In addition, pg_upgrade verifies that the clusters are compatible.
The pg_upgrade program helps you upgrade from PostgreSQL 8.4.X to the current DBMS release.
Let's look at a general upgrade plan using pg_upgrade.
- Move the old cluster. This must be done if the directory was not linked to the old release. For example, it is located at
/usr/local/pgsql. If you do not rename it, a conflict will occur during the update.
Renaming is performed when the server is turned off with the command:
mv /usr/local/pgsql /usr/local/pgsql.old- Build a new version from source. Adjust configure so that the flags are compatible with the old cluster configuration. Before starting the upgrade,
pg_upgradewill check the compatibility of the flags. - Install new executable files. To place the server in a non-standard directory, use
prefix:
make prefix=/usr/local/pgsql.new install- Use
initdbto initialize a new cluster. Check that the flags are compatible with the flags of the old cluster. - Install extension shared object files. If updates are available,
pg_upgradewill automatically create a script for their subsequent installation. - Transfer full text search files.
- Configure peer authentication in
pg_hba.conf. This needs to be done because pg_upgrade will connect to the old and new hosts multiple times. - Check that the old and new servers are stopped:
pg_ctl -D /opt/PostgreSQL/9.6 stop
pg_ctl -D /opt/PostgreSQL/14 stop- Run
pg_upgradefrom the new server. First, call the program in check mode by adding the flag:
pg_upgrade --checkIn response, you will receive a list of adjustments that need to be made manually after the upgrade.
- If you made changes to the
pg_hba.conffile, restore it to its previou state. Sometimes, other configurations on the new cluster need to be changed to match the settings of the old cluster. - Start the server.
If you are satisfied with the result, delete the old cluster.
For details and features of using pg_upgrade, see the documentation.
Using Logical Replication
Starting from the 10th version of PostgreSQL, we have a method for logical replication of data objects and changes in them. It is based on the use of replication identifiers; usually, they are primary keys.
Logical replication uses the publish-subscribe model. The user creates a snapshot of the published database and copies it to the subscriber. In the PostgreSQL documentation, one of the typical scenarios for using logical replication is precisely the replication between different major versions of PostgreSQL.
The backup server can be located on the same or different host. After synchronization is complete, various options are available. For example, you can make the new server the main one and turn off the old one.
The main advantage of logical replication is minimal downtime. Server interruption, in some cases, is limited to a few seconds.
Conclusion
We looked at universal methods for upgrading PostgreSQL to a new major version and updating within one major version.
Using this guide, you can select the method that works best for your particular case and update your current version of PostgreSQL.
Hostman provides pre-configured and ready-to-use cloud databases, including cloud PostgreSQL.
Frequently Asked Questions
How to check PostgreSQL version on my server?
You can run psql --version in the terminal or connect to your DB and run SELECT version();.
How do I upgrade PostgreSQL to a new version?
Use pg_dumpall to back up, install the new version, and restore your data.
What is the difference between pg_upgrade and pg_dump?
pg_upgrade is quicker and keeps configs. pg_dump is better if you want full control.
Do I need to uninstall the old PostgreSQL version?
Nope, you can keep both temporarily to ease migration.
PostgreSQL
16.06.2025
Reading time: 8 min
Share
Similar
PostgreSQL
How to Migrate a PostgreSQL Database to Another Server
When working with PostgreSQL, you might eventually need to transfer a database from one server to another. Despite seeming complex, PostgreSQL migration can be accomplished using PostgreSQL's built-in tools.
This article outlines various methods to transfer a PostgreSQL database from one server to another on Ubuntu 22.04.
Prerequisites
To migrate a Postgres database, you will need:
A current server running Ubuntu 22.04 with PostgreSQL installed. The database to be transferred should already exist in PostgreSQL.
A new cloud server or virtual machine with Ubuntu 22.04 and affordable cloud PostgreSQL pre-installed. This article uses PostgreSQL version 15. On Hostman, you can easily deploy such a server by choosing the PostgreSQL image when creating a server.
Optionally, the pgAdmin client application for connecting and managing PostgreSQL databases. PgAdmin can be installed on any OS, including Windows, Linux, and macOS.
Preparation Before Database Transfer
Before starting the Postgres migration, ensure that both servers have:
A network connection between them. Use tools like ping and telnet to verify this (with telnet, check ports 22 and 5432).
Ports 22 (SSH) and 5432 (PostgreSQL) open.
Enough free disk space.
Configuring PostgreSQL for Remote Connections
Ensure PostgreSQL can accept remote connections on both servers:
Edit the postgresql.conf file. If using a different version, replace 15 with your version number:
nano /etc/postgresql/15/main/postgresql.conf
Find the listen_addresses parameter. By default, it is commented out and accepts only local connections (localhost). Allow remote connections from specific addresses or all addresses (for testing purposes, use *):
listen_addresses = '*'
In production environments, specify only the required addresses.
Save and exit the file.
Edit the pg_hba.conf file:
nano /etc/postgresql/15/main/pg_hba.conf
Find the line for IPv4 local connections (# IPv4 local connections) and update it to allow connections from specific addresses or all addresses for testing:
host all all 0.0.0.0/0 md5
Save and exit the file.
Restart the PostgreSQL server:
systemctl restart postgresql
Set a strong password for the PostgreSQL user on both servers:
sudo -i -u postgres psql
In the psql console run:
ALTER USER postgres WITH PASSWORD 'nsH7z*0kl>&7?7';
Where postgres is the username and nsH7z*0kl>&7?7 is the password.
Transferring the Database Using pg_dump and psql
Typically, transferring a database involves three steps:
Creating a backup of the database.
Transferring the backup to the target server.
Restoring the database on the new server.
These steps can be performed using PostgreSQL's pg_dump and psql tools.
Transferring a Database
For instance, let's look at transferring a database named e_commerce from a server with IP 166.1.227.252 to a server with IP 91.206.179.207.
Create the target database beforehand:
CREATE DATABASE e_commerce;
Then run:
pg_dump -h localhost -U postgres e_commerce | psql -h 91.206.179.207 -U postgres e_commerce
Explanation:
pg_dump creates a backup of the database.
-h localhost specifies the database server address.
-U postgres specifies the username.
e_commerce is the database name on the current and new server.
psql connects to the remote PostgreSQL server and loads the database.
-h 91.206.179.207 specifies the target server address.
Transferring Without Remote Access
If remote access is unavailable, save the database to a file, transfer it using scp, and restore it on the target server:
pg_dump -h localhost -U postgres e_commerce > e_commerce.sql && scp e_commerce.sql db-admin@91.206.179.207:/var/lib/postgresql
When executing the command, the password for the postgres system user will be requested, not the password for the postgres user defined within the database.
Where:
pg_dump creates a database backup;
-h localhost is the address of the server (IP address or domain name) where the database is located. In this example, the database server address matches the server address itself. If the database is on another server and network access is available, you can specify the address of the remote database;
-U postgres is the username used to connect to the database;
e_commerce is the name of the database to be transferred;
e_commerce.sql is the name of the file in .sql format where the database will be saved;
scp is a utility for secure file copying between hosts. It uses the SSH protocol for data transfer and protection;
db-admin@91.206.179.207:/var/lib/postgresql means username_on_remote_server@address_of_remote_server:full_path where the backup file will be saved.
After entering the command, you first need to enter the password for the database user account (in this example, it is the postgres user), and then enter the password for the remote server user (in this example, it is the db-admin user).
Now you need to upload the file to the database. Run these commands on the target server.
Create a database in psql:
CREATE DATABASE e_commerce;
Then, exit psql and run in the terminal:
psql e_commerce < e_commerce.sql
Creating a Compressed Archive
For larger databases, create a compressed archive:
pg_dump -h localhost -U postgres e_commerce > e_commerce.tar.gzip && scp e_commerce.tar.gzip db-admin@91.206.179.207:/var/lib/postgresql
Restore from the archive:
psql e_commerce < e_commerce.tar.gzip
Adding a Timestamp to the Archive Name
If you need to know the exact date and time that the database was backed up, you can include it in the name of the file being created.
To do this, use the date command and the date format. The example below will use the day-month-year date format:
pg_dump -h localhost -U postgres e_commerce > e_commerce_$(date +%d-%m-%y).sql
Transferring the Database Using pgAdmin
Alternatively, you can use pgAdmin's graphical interface for the Postgres database migration.
Backup Database
Launch pgAdmin: Open pgAdmin and connect to your PostgreSQL server.
Register Server: Right-click on Object Explorer, select Register, then Server.
Configure Connection:
Name: In the General tab, enter a name for the connection (e.g., my_db).
Next, go to the Connection tab and specify:
Host name/address: Specify the IP address or domain name of the PostgreSQL server.
Port: Default is 5432; change if needed.
Maintenance database: Name of the database for backup.
Username and Password: Enter credentials for database access.
Connect: Click Save to connect. If successful, the database appears on the left sidebar.
Backup Database:
Right-click on the database name and select Backup.
Set a Filename for the backup file.
Choose a Format and Encoding (UTF8 recommended).
Select specific Objects to include.
Click Backup to start.
Restore Database
Prepare New Database:
Open psql shell.
Execute:
CREATE DATABASE e_commerce;
Connect to PostgreSQL Server:
In pgAdmin, connect to the new PostgreSQL server, selecting e_commerce as the database.
Restore Database:
Right-click on the database name and choose Restore.
Set the Format (ensure it matches the backup file).
Specify the Filename of the backup file.
Click Restore to begin.
Wait for the Process completed confirmation.
Conclusion
PostgreSQL offers several methods to migrate databases between servers, including using built-in tools for flexible and robust database backups and transfers.
If you've ran into some troubles, check our instruction on how to create server on Ubuntu.
15 May 2025 · 6 min to read
PostgreSQL
Installing PostgreSQL on Debian
PostgreSQL is an advanced open-source relational database management system (DBMS). There are two ways to install it on Debian 11: from the operating system repository or the official PostgreSQL repository.
In this article, you will learn both methods, as well as how to perform common operations like creating roles and databases. Don't forget to check how to configure static IP address on Debian.
Installation from the Debian 11 repository
On Debian, you can install PostgreSQL directly from the system repository.
First, update your package list. Launch the terminal and run:
sudo apt update && sudo apt upgrade
The PostgreSQL package is available in the Debian repository, so you can install it using the apt utility. To do this, run:
sudo apt install postgresql postgresql-contrib
Once the installation is complete, check the status of the service using the command:
sudo systemctl status postgresql
If the service does not start automatically, you can start it manually. To do this, run:
sudo systemctl start postgresql
To stop a running service, run:
sudo systemctl stop postgresql
Before configuring PostgreSQL on Debian, make sure the service is running.
Installation from the official PostgreSQL repository
If you want to use only the latest versions of Postgres, we recommend using the official PostgreSQL repository for installation and subsequent updates.
First of all, you need to add the GPG signing key. This is a security requirement to verify the authenticity of the PostgreSQL repository. To do this, launch a terminal and run:
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg
Now you are ready to add the Postgres repository. Use the following command:
echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/bulseye-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list
After successfully integrating the PostgreSQL library, you can install the DBMS. But before you do that, update the system repository using the command:
sudo apt update
After updating, run the following command to install PostgreSQL on Debian:
sudo apt install postgresql
Installation is completed. Now you can proceed to the basic configuration of PostgreSQL.
Basic setup
When installing Postgres, the postgres user is automatically created. You can use this account for your first connection.
Switch to the postgres user:
sudo su - postgres
Run the psql utility which is a shell for managing PostgreSQL:
psql
You can now interact with the PostgreSQL server. To exit the shell, enter:
\q
You can use the following command to access the Postgres command line without switching users:
sudo -u postgres psql
However, the postgres user is usually only used from localhost. If, for example, you use cloud databases, it is better to create a new role for the connection.
Creating a role and a database
The createuser command allows you to create new roles from the command line. Only superusers and roles with CREATEROLE privileges can create new roles.
In the following example, we will create a new role named hostman and a database named hostman_db, and then grant the new role privileges to manage the database.
First create a new role:
sudo su - postgres -c "createuser hostman"
Then create a new database:
sudo su - postgres -c "createdb hostman_db"
To grant the user permissions to the database, connect to the shell:
sudo -u postgres psql
Run the following query to grant the hostman user privileges to manage the hostman_db database:
GRANT ALL PRIVILEGES ON DATABASE hostman_db TO hostman;
You can create new roles and databases in the PostgreSQL shell. In this case, the syntax will be slightly different.
To create a new role with a password, run:
create user cloud with password 'hostmancloud';
To create a new database, run:
create database cloud_db;
Then you must also grant all privileges with the GRANT ALL PRIVILEGES ON DATABASE … TO … command.
Setting up remote access
By default, the Postgres server only listens on the local interface 127.0.0.1. This may be inconvenient. Let's say you have a server on Hostman with PostgreSQL installed. It will be much more convenient to connect to it remotely. To do this, you need to configure the server to listen to other network interfaces.
To change the configuration, open the postgresql.conf file using any editor. This example uses the nano editor:
sudo nano /etc/postgresql/12/main/postgresql.conf
Find the CONNECTIONS AND AUTHENTICATION section and the line #listen_addresses = 'localhost' in the configuration file. Change the line value to listen_addresses = '*'. If you want the server to listen not to all network interfaces, but only to the selected one, specify it instead of an asterisk.
Save the file and restart the Postgres service for the changes to take effect:
sudo service postgresql restart
The last step is to allow connections from the network. To install it, you need to edit the pg_hba.conf file. Open it in the editor:
sudo nano /etc/postgresql/12/main/pg_hba.conf
Find the IPv4 local connections line. Specify the desired network. For example, like this:
TYPE
DATABASE
USER
ADDRESS
METHOD
host
all
hostman
38.62.228.244
md5
You can use other authentication methods. For a complete list, see the PostgreSQL documentation.
Conclusion
There are two ways to install managed PostgreSQL on Debian.
The first option is to use the system repository. Its main advantage is speed. There is no need to install anything additional, just run one command. The downside is that the system repository does not always contain the latest version of the software.
The second installation option is to use the official PostgreSQL repository. This method ensures that you are using the latest version of the DBMS. But you will have to perform a few more steps: first, add the official repository itself and only then install Postgres from it.
09 May 2025 · 5 min to read
PostgreSQL
PostgreSQL Triggers: Creation, Deletion, Examples
Triggers are used to instruct the PostgreSQL engine to execute a piece of code when a specific event occurs. They act like a catalyst for changes — a trigger that initiates a chain of events.
A trigger must be associated with a specific table, view (pseudo-table), or foreign table. It activates only when operations are performed on that entity — INSERT, UPDATE, DELETE, or TRUNCATE. Depending on your needs, the trigger can run before, after, or instead of the event/operation.
Types of Triggers
PotgreSQL triggers are divided into two types depending on the level at which they operate:
FOR EACH ROW: The function is called for each individual row affected by the event. For example, an UPDATE affecting 100 rows will trigger the function 100 times — once for each row.
FOR EACH STATEMENT: The function is called just once per SQL statement, regardless of how many rows are affected.
Triggers Use Cases
Triggers are powerful tools with many use cases. Some examples include:
Tracking Changes: You can use triggers to log transaction details when changes occur in a table.
Data Validation: Triggers can enforce constraints before applying changes to the database.
Auto-Filling Fields: Automatically populate fields based on new transaction data.
Performance Optimization: For example, on a server like Hostman, you might log timestamps in a table and want to aggregate that data every 6 hours (four intervals a day). Scanning and aggregating all rows repeatedly is inefficient on large datasets.
Instead of recalculating everything each time, you can use Materialized Views, which cache results. However, these are recalculated entirely on each refresh, which is still problematic on large datasets.
Triggers can solve this by acting like "smart" materialized views: updating only the affected row, not the entire dataset.
Creating a Trigger
Let’s look at how to create a trigger in PostgreSQL.
Syntax:
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments );
Where the event can be one or more of the following:
INSERT
UPDATE [ OF column_name [, ...] ]
DELETE
TRUNCATE
Key notes:
You can create (CREATE) or replace (REPLACE) an existing trigger.
You associate the function directly with a table/view/foreign table. The code runs only when the specified event occurs on that entity.
Triggers using INSTEAD OF must be marked with FOR EACH ROW and are only allowed on views.
Triggers using BEFORE or AFTER on views must be FOR EACH STATEMENT.
Refer to PostgreSQL documentation for a summary table that outlines these rules.
Basic Examples of PostgreSQL Triggers
Trigger before update:
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
Trigger before update of a specific column:
CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
Trigger after update to log changes:
CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_account_update();
INSTEAD OF trigger for view insertions:
CREATE TRIGGER view_insert
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE FUNCTION view_insert_row();
Trigger on delete:
CREATE TRIGGER example_delete_trigger
AFTER DELETE ON my_view
FOR EACH ROW
EXECUTE PROCEDURE aft_delete();
Practical Example: Inserting Into Two Tables
Let’s create a PostgreSQL trigger that adds information about a new employee to a second table when data is inserted into the primary table.
Step 1: Create both tables:
CREATE TABLE "Employee" (
"EmployeeId" INT NOT NULL,
"LastName" VARCHAR(20) NOT NULL,
"FirstName" VARCHAR(20) NOT NULL,
"Title" VARCHAR(30),
"ReportsTo" INT,
"BirthDate" TIMESTAMP,
"HireDate" TIMESTAMP,
"Address" VARCHAR(70),
"City" VARCHAR(40),
"State" VARCHAR(40),
"Country" VARCHAR(40),
"PostalCode" VARCHAR(10),
"Phone" VARCHAR(24),
"Fax" VARCHAR(24),
"Email" VARCHAR(60),
CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId")
);
CREATE TABLE "Employee_Audit" (
"EmployeeId" INT NOT NULL,
"LastName" VARCHAR(20) NOT NULL,
"FirstName" VARCHAR(20) NOT NULL,
"UserName" VARCHAR(20) NOT NULL,
"EmpAdditionTime" VARCHAR(20) NOT NULL
);
Step 2: Create the trigger function:
CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc()
RETURNS trigger AS
$$
BEGIN
INSERT INTO "Employee_Audit" ("EmployeeId", "LastName", "FirstName", "UserName", "EmpAdditionTime")
VALUES (NEW."EmployeeId", NEW."LastName", NEW."FirstName", current_user, current_date);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
Step 3: Create the trigger:
CREATE TRIGGER employee_insert_trigger
AFTER INSERT
ON "Employee"
FOR EACH ROW
EXECUTE PROCEDURE employee_insert_trigger_fnc();
Test:
Insert a record:
INSERT INTO "Employee"
VALUES(12, 'Smith', 'Jeff', 'Editor', 1, '1992-05-28 00:00:00', '2022-01-15 00:00:00',
'Paseo de Gracia', 'Barcelona', 'Catalonia', 'Spain', '128 665', '+15 52-469-2573',
'+15 52-469-2573', 'mail@mail.com');
Check that the data is there:
SELECT * FROM "Employee" WHERE "EmployeeId" = 12;
Check the audit log:
SELECT * FROM "Employee_Audit";
You should see something like:
EmployeeId | 12
LastName | Smith
FirstName | Jeff
UserName | postgres
EmpAdditionTime | 2025-04-10
Perfect! It works as expected.
Modifying a Trigger
To change a trigger's properties, use CREATE OR REPLACE TRIGGER and specify the same trigger function and table. You can update the remaining properties as needed.
To rename a trigger:
ALTER TRIGGER name ON table_name RENAME TO new_name;
Check documentation for more details.
Deleting a Trigger
To delete a trigger:
DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ];
Example:
DROP TRIGGER some_example_of_trigger ON "Example";
Note:
You must own the table to delete its trigger.
Use IF EXISTS to avoid errors if the trigger doesn’t exist.
Use CASCADE to remove all dependent objects.
Use RESTRICT to prevent deletion if dependencies exist (default).
Check the documentation for more details.
Important Notes
You must have TRIGGER privilege on the table and EXECUTE privilege on the trigger function.
You can check the system catalog pg_trigger to view existing triggers.
If you define multiple triggers for the same table/event, they execute alphabetically by name.
11 April 2025 · 6 min to read
Do you have questions,
comments, or concerns?
Our professionals are available to assist you at any moment,
whether you need help or are just unsure of where to start.
whether you need help or are just unsure of where to start.
