GAMMU-SMSD-PGSQL(7)

📦

version 1.45.0 · 5 versions

1.45.0 · arch rolling (latest)
1.43.3 · nixos 26.05
1.44.1 · nixpkgs unstable
1.44.0 · ubuntu stonking
1.42.0 · alpine 3.24, debian bookworm, debian trixie, epel 9, fedora 43, fedora 44, ubuntu jammy, ubuntu noble, ubuntu questing, ubuntu resolute

okay bestie, welcome to GAMMU-SMSD-PGSQL(7) — literally the manual page for the PostgreSQL backend of gammu-smsd(1), and it's giving "message storage but make it database." 💅✨

🎀 NAME 🎀

gammu-smsd-pgsql — the gammu-smsd(1) backend that uses a PostgreSQL database server as a message storage. periodt. 💖

🌸 DESCRIPTION 🌸

okay so the PGSQL backend stores all the data in a PostgreSQL database server — yes, the one at https://www.postgresql.org/ — and its parameters are defined by configuration. Peep SMSD Configuration File at #gammu-smsdrc for the description of configuration options, bestie. 🫶

For the tables description, go see SMSD Database Structure at #gammu-smsd-tables. iykyk.

This backend is based on SQL Service at #gammu-smsd-sql. no cap. 💜

🦋 CONFIGURATION 🦋

Before running gammu-smsd — cross-ref #gammu-smsd, obviously — you need to create the necessary tables in the database, which is described below 👇.

The configuration file can then look like this, fr fr:

[smsd]
service = sql
driver = native_pgsql
host = localhost

🌸 CREATING TABLES FOR POSTGRESQL 🌸

okay so this is the part where we actually build the thing 💅 here's the SQL script for creating tables in a PostgreSQL database — like, the whole entire script, no cuts:

--
-- Database: "smsd"
--
-- CREATE USER "smsd" WITH NOCREATEDB NOCREATEUSER;
-- CREATE DATABASE "smsd" WITH OWNER = "smsd" ENCODING = 'UTF8';
-- \connect "smsd" "smsd"
-- COMMENT ON DATABASE "smsd" IS 'Gammu SMSD Database';

-- --------------------------------------------------------

--
-- Function declaration for updating timestamps
--
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
  BEGIN
    NEW."UpdatedInDB" := LOCALTIMESTAMP(0);
    RETURN NEW;
  END;
$update_timestamp$ LANGUAGE plpgsql;

-- --------------------------------------------------------

--
-- Sequence declarations for tables' primary keys
--

--CREATE SEQUENCE inbox_ID_seq;

--CREATE SEQUENCE outbox_ID_seq;

--CREATE SEQUENCE outbox_multipart_ID_seq;

--CREATE SEQUENCE sentitems_ID_seq;

-- --------------------------------------------------------

--
-- Index declarations for tables' primary keys
--

--CREATE UNIQUE INDEX inbox_pkey ON inbox USING btree ("ID");

--CREATE UNIQUE INDEX outbox_pkey ON outbox USING btree ("ID");

--CREATE UNIQUE INDEX outbox_multipart_pkey ON outbox_multipart USING btree ("ID");

--CREATE UNIQUE INDEX sentitems_pkey ON sentitems USING btree ("ID");

-- --------------------------------------------------------

--
-- Table structure for table "gammu"
--

CREATE TABLE gammu (
  "Version" smallint NOT NULL DEFAULT 0 PRIMARY KEY
);

--
-- Dumping data for table "gammu"
--

INSERT INTO gammu ("Version") VALUES (18);

-- --------------------------------------------------------

--
-- Table structure for table "inbox"
--

CREATE TABLE inbox (
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "ReceivingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "Text" text NOT NULL,
  "SenderNumber" varchar(20) NOT NULL DEFAULT '',
  "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
  "UDH" text NOT NULL,
  "SMSCNumber" varchar(20) NOT NULL DEFAULT '',
  "Class" integer NOT NULL DEFAULT -1,
  "TextDecoded" text NOT NULL DEFAULT '',
  "ID" serial PRIMARY KEY,
  "MessageID" integer NOT NULL DEFAULT 0,
  "SequencePosition" integer NOT NULL DEFAULT 1,
  "PartCount" integer NOT NULL DEFAULT 1,
  "RecipientID" text NOT NULL,
  "Processed" boolean NOT NULL DEFAULT FALSE,
  "Status" integer NOT NULL DEFAULT -1,
  CHECK ("Coding" IN
  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
);

CREATE INDEX inbox_message ON inbox("MessageID", "SequencePosition");
CREATE INDEX inbox_insert ON inbox("InsertIntoDB");

--
-- Dumping data for table "inbox"
--

-- --------------------------------------------------------

--
-- Create trigger for table "inbox"
--

CREATE TRIGGER update_timestamp BEFORE UPDATE ON inbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

-- --------------------------------------------------------

--
-- Table structure for table "outbox"
--

CREATE TABLE outbox (
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "SendingDateTime" timestamp NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "SendBefore" time NOT NULL DEFAULT '23:59:59',
  "SendAfter" time NOT NULL DEFAULT '00:00:00',
  "SendDays" integer NOT NULL DEFAULT 127,
  "Text" text,
  "DestinationNumber" varchar(20) NOT NULL DEFAULT '',
  "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
  "UDH" text,
  "Class" integer DEFAULT -1,
  "TextDecoded" text NOT NULL DEFAULT '',
  "ID" serial PRIMARY KEY,
  "MultiPart" boolean NOT NULL DEFAULT FALSE,
  "RelativeValidity" integer DEFAULT -1,
  "SenderID" varchar(255),
  "SendingTimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "DeliveryReport" varchar(10) DEFAULT 'default',
  "CreatorID" text NOT NULL,
  "Retries" integer DEFAULT 0,
  "Priority" integer DEFAULT 0,
  "Status" varchar(255) NOT NULL DEFAULT 'Reserved',
  "StatusCode" integer NOT NULL DEFAULT -1,
  CHECK ("Coding" IN
  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
  CHECK ("DeliveryReport" IN ('default','yes','no')),
  CHECK ("Status" IN
  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
  'DeliveryUnknown','Error','Reserved'))
);

CREATE INDEX outbox_date ON outbox("SendingDateTime", "SendingTimeOut");
CREATE INDEX outbox_sender ON outbox("SenderID");

--
-- Dumping data for table "outbox"
--

-- --------------------------------------------------------

--
-- Create trigger for table "outbox"
--

CREATE TRIGGER update_timestamp BEFORE UPDATE ON outbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

-- --------------------------------------------------------

--
-- Table structure for table "outbox_multipart"
--

CREATE TABLE outbox_multipart (
  "Text" text,
  "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
  "UDH" text,
  "Class" integer DEFAULT -1,
  "TextDecoded" text DEFAULT NULL,
  "ID" serial,
  "SequencePosition" integer NOT NULL DEFAULT 1,
  "Status" varchar(255) NOT NULL DEFAULT 'Reserved',
  "StatusCode" integer NOT NULL DEFAULT -1,
  PRIMARY KEY ("ID", "SequencePosition"),
  CHECK ("Coding" IN
  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
  CHECK ("Status" IN
  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
  'DeliveryUnknown','Error','Reserved'))
);

--
-- Dumping data for table "outbox_multipart"
--

-- --------------------------------------------------------

--
-- Table structure for table "phones"
--

CREATE TABLE phones (
  "ID" text NOT NULL,
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "TimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "Send" boolean NOT NULL DEFAULT 'no',
  "Receive" boolean NOT NULL DEFAULT 'no',
  "IMEI" varchar(35) PRIMARY KEY NOT NULL,
  "IMSI" varchar(35) NOT NULL,
  "NetCode" varchar(10) DEFAULT 'ERROR',
  "NetName" varchar(35) DEFAULT 'ERROR',
  "Client" text NOT NULL,
  "Battery" integer NOT NULL DEFAULT -1,
  "Signal" integer NOT NULL DEFAULT -1,
  "Sent" integer NOT NULL DEFAULT 0,
  "Received" integer NOT NULL DEFAULT 0
);

--
-- Dumping data for table "phones"
--

-- --------------------------------------------------------

--
-- Create trigger for table "phones"
--

CREATE TRIGGER update_timestamp BEFORE UPDATE ON phones FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

-- --------------------------------------------------------

--
-- Table structure for table "sentitems"
--

CREATE TABLE sentitems (
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "SendingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "DeliveryDateTime" timestamp(0) WITHOUT time zone NULL,
  "Text" text NOT NULL,
  "DestinationNumber" varchar(20) NOT NULL DEFAULT '',
  "Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
  "UDH" text NOT NULL,
  "SMSCNumber" varchar(20) NOT NULL DEFAULT '',
  "Class" integer NOT NULL DEFAULT -1,
  "TextDecoded" text NOT NULL DEFAULT '',
  "ID" serial,
  "SenderID" varchar(255) NOT NULL,
  "SequencePosition" integer NOT NULL DEFAULT 1,
  "Status" varchar(255) NOT NULL DEFAULT 'SendingOK',
  "StatusError" integer NOT NULL DEFAULT -1,
  "TPMR" integer NOT NULL DEFAULT -1,
  "RelativeValidity" integer NOT NULL DEFAULT -1,
  "CreatorID" text NOT NULL,
  "StatusCode" integer NOT NULL DEFAULT -1,
  CHECK ("Status" IN
  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
  'DeliveryUnknown','Error')),
  CHECK ("Coding" IN
  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
  PRIMARY KEY ("ID", "SequencePosition")
);

CREATE INDEX sentitems_date ON sentitems("DeliveryDateTime");
CREATE INDEX sentitems_tpmr ON sentitems("TPMR");
CREATE INDEX sentitems_dest ON sentitems("DestinationNumber");
CREATE INDEX sentitems_sender ON sentitems("SenderID");

--
-- Dumping data for table "sentitems"
--

-- --------------------------------------------------------

--
-- Create trigger for table "sentitems"
--

CREATE TRIGGER update_timestamp BEFORE UPDATE ON sentitems FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

like, look at all that 😭 six tables (gammu, inbox, outbox, outbox_multipart, phones, sentitems), the update_timestamp() function, the triggers on every single one — she did the MOST. and yes bestie, those CHECK constraints are doing their job keeping your "Coding" and "Status" values honest 💅

Note: you can find the script in docs/sql/pgsql.sql as well 🫶 like, it's already sitting there in the repo, no cap.

🦋 UPGRADING TABLES 🦋

so your database is old and you wanna glow it up ✨ the easiest way to upgrade the database structure is to back up the old one and start fresh with a new one created from the example above. backup first, always, i'm literally begging 🙈

if you'd rather upgrade the database you already have (respect, that's main character energy), you can use the changes described in History of database structure <#smsd-tables-history> and then manually update the Version field in the gammu table. that's it, that's the whole move 💫

👑 Author

Michal Čihař <michal@cihar.com>

🎀 Copyright

2009-2015, Michal Čihař <michal@cihar.com>

okay that's the whole page, we ate and left zero crumbs 🍓 this was GAMMU-SMSD-PGSQL(7) from the 1.45.0 era, dated Sep 16, 2026 — go build your tables bestie, you got this 💖✨🎀

📦 where this page comes from

arch rolling: gammu 1.45.0-1 · GPL-2.0-or-later
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash-1017 on 2026-11-04: a language model's rewrite of the original, under its terms