Migrate WordPress Content to PostgreSQL

Moving WordPress content to PostgreSQL is not a database-engine conversion. WordPress stores relational content and metadata in MySQL or MariaDB, uploaded files under wp-content/uploads, and presentation details inside HTML and block markup. A safe migration extracts those parts, maps them into the new application's schema, copies media separately, and verifies the rendered URLs.

Back up before exporting

Take a provider-supported database backup and a filesystem backup that includes wp-content/uploads, themes, plugins, and relevant configuration. Record the WordPress version, site URL, table prefix, active plugins, and backup timestamp. Restore the backup in an isolated environment before relying on it.

Keep the original site available in read-only form during validation if the project permits it. Freeze editorial changes for the final cutover or plan a delta import so posts published after the first export are not lost.

Choose the source export

WordPress Tools → Export produces a WXR XML file. An “All content” export includes posts, pages, comments, custom fields, taxonomy terms, menus, custom post types, and users referenced by content. It is convenient and portable, but plugin-owned data may need separate handling.

The REST API exposes structured posts, pages, media, categories, tags, and users when endpoints and permissions permit. It supports pagination and fields such as slug, date_gmt, content, author, categories, tags, and featured_media.

A database dump offers the most complete raw source but couples the importer to WordPress tables, serialized metadata, plugin schemas, and the installation's table prefix. Do not point the new application directly at the production WordPress database for ongoing reads.

Whichever source you choose, retain a stable source_id and source_type so reruns update the same target row.

Inventory what must move

Map at least:

• posts, pages, and required custom post types;
• titles, excerpts, HTML/block content, status, and menu order;
• unique slugs and parent-child page relationships;
• authors and attribution policy;
• categories, tags, custom taxonomies, and relationships;
• published and modified dates, preferably the GMT values;
• approved metadata and custom fields;
• featured media, inline media, captions, and alternative text;
• canonical paths and every old URL requiring a redirect.

Do not copy password hashes or user accounts casually. Authentication migration is a separate security project with consent, password reset, role mapping, and personal-data requirements.

Design a PostgreSQL target schema

One possible normalized model is:

CREATE TABLE author (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_id bigint UNIQUE,
  display_name text NOT NULL,
  slug text NOT NULL UNIQUE
);
 
CREATE TABLE content_item (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_type text NOT NULL,
  source_id bigint NOT NULL,
  kind text NOT NULL CHECK (kind IN ('post', 'page')),
  slug text NOT NULL,
  title text NOT NULL,
  excerpt text,
  body_html text NOT NULL,
  status text NOT NULL,
  author_id bigint REFERENCES author(id),
  published_at timestamptz,
  modified_at timestamptz,
  parent_source_id bigint,
  metadata jsonb NOT NULL DEFAULT '{}',
  UNIQUE (source_type, source_id),
  UNIQUE (kind, slug)
);
 
CREATE TABLE term (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  taxonomy text NOT NULL,
  source_id bigint NOT NULL,
  name text NOT NULL,
  slug text NOT NULL,
  UNIQUE (taxonomy, source_id),
  UNIQUE (taxonomy, slug)
);
 
CREATE TABLE content_term (
  content_id bigint REFERENCES content_item(id) ON DELETE CASCADE,
  term_id bigint REFERENCES term(id) ON DELETE CASCADE,
  PRIMARY KEY (content_id, term_id)
);

This is an example, not a drop-in schema. If posts and pages can share a slug under different URL structures, model the full canonical path or parent hierarchy instead of forcing the shown uniqueness rule. Put frequently queried, validated fields in columns; reserve jsonb for metadata whose schema genuinely varies.

Preserve HTML without trusting it

WordPress content.rendered can contain HTML, shortcodes, block comments, embedded forms, plugin markup, and absolute URLs. Decide whether the new renderer will:

• preserve supported HTML;
• transform Gutenberg blocks into a new structured representation;
• replace shortcodes and plugin embeds;
• remove unsupported scripts, styles, iframes, and event attributes.

Sanitize on import with a documented allowlist and sanitize again or render safely at the output boundary. Do not use regular expressions as a general HTML parser. Store the original source payload in restricted migration storage so transformations can be reproduced without making unsafe markup public.

Build an idempotent import

Process terms and authors before content, then resolve relationships and media. Use parameterized queries and an upsert keyed by the WordPress identity:

INSERT INTO content_item (
  source_type,
  source_id,
  kind,
  slug,
  title,
  excerpt,
  body_html,
  status,
  published_at,
  modified_at
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
ON CONFLICT (source_type, source_id)
DO UPDATE SET
  slug = EXCLUDED.slug,
  title = EXCLUDED.title,
  excerpt = EXCLUDED.excerpt,
  body_html = EXCLUDED.body_html,
  status = EXCLUDED.status,
  published_at = EXCLUDED.published_at,
  modified_at = EXCLUDED.modified_at;

Use a bounded transaction per batch rather than one enormous transaction. Save a migration run ID, source checksum, status, and error without logging private content. A rerun should converge on the same rows and relationships rather than append duplicates.

When reading the REST API, follow pagination headers and authenticate when private or edit-context fields are required. Rate-limit requests and retry only transient, idempotent reads.

Move media separately

A content database migration does not automatically move uploaded images. WordPress normally stores media files beneath wp-content/uploads, while attachment records hold metadata and URLs.

Copy original files and required generated sizes to the new public or object-storage location. Build a media manifest containing source attachment ID, old URL, new URL, checksum, MIME type, byte size, dimensions, alt text, and migration status. Validate the decoded file type rather than trusting an extension.

Rewrite URLs by parsing HTML attributes such as src, srcset, href, and block data. A blind SQL string replacement can corrupt serialized WordPress values, unrelated text, or encoded URLs. Keep redirects for old attachment URLs when external sites may link to them.

Preserve SEO and links

Create an explicit mapping from every indexable old URL to its canonical new URL:

/2024/05/example-post/  -> /blog/example-post
/about-us/              -> /about

Serve permanent redirects only after verifying each target. Preserve slugs where possible, publication dates where displayed, titles, descriptions, headings, canonical tags, index/noindex intent, and structured data that remains accurate. Generate the new sitemap and update internal links rather than forcing them through redirects.

Do not redirect every missing page to the home page. Retired content may need a relevant replacement or an honest 404/410 according to the site's SEO plan.

Validate before cutover

Compare counts by content type and status, not just a grand total. Sample the oldest, newest, longest, nested, media-heavy, and plugin-dependent content. Check:

• unique slugs and canonical paths;
• author and taxonomy relationship counts;
• UTC and displayed publication times;
• rendered headings, lists, code, embeds, and links;
• every referenced media URL and checksum;
• old-to-new redirects without loops or chains;
• sitemap URLs, canonical tags, metadata, and HTTP status;
• drafts and private content remaining inaccessible.

Keep a rollback path until production traffic, logs, and crawler reports show the cutover is stable. For PostgreSQL connection formatting during the importer setup, use PostgreSQL Connection URL Explained.

Verification checklist

• A database and filesystem backup was restored successfully in isolation.
• The export includes required plugin and custom-type data or has a separate plan.
• Import rows have stable source identities and reruns are idempotent.
• HTML is parsed, transformed, and sanitized by explicit policy.
• wp-content/uploads files moved separately and passed broken-link checks.
• Counts, relationships, dates, visibility, and representative rendering match.
• Every important old URL has a verified redirect or intentional retirement status.

References

Documentation checked on 2026-08-12:

• WordPress Tools Export and WXR contents
• WordPress REST API: posts
• WordPress REST API: pages
• WordPress REST API: media
• WordPress REST API: categories and tags
• WordPress database description
• PostgreSQL INSERT ... ON CONFLICT