How to Export Akeneo Products to PostgreSQL: Step-by-Step Tutorial
This tutorial covers two paths: the manual Python script approach (so you understand what's involved), and the SyncPIM no-code path (so you can skip the boilerplate). Both result in a live PostgreSQL database with your Akeneo products.
Prerequisites
Akeneo
- • Akeneo instance URL
- • API connection with Client ID + Secret
- • API user Username + Password
- • At least one channel configured
PostgreSQL
- • PostgreSQL 12+ (for JSONB path operators)
- • Database with CREATE TABLE permissions
- • Connection string or host/user/pass/db
- • Port 5432 accessible from SyncPIM IPs
To get Akeneo API credentials: go to Connect → Connection Settings → Add connection in your Akeneo admin. See the authentication guide for screenshots.
Option A: Manual Python script
Here's what a minimal Akeneo → PostgreSQL export script looks like. This shows the complexity you're taking on when you build it yourself.
Step 1 — Authenticate
import requests
import psycopg2
import json
AKENEO_URL = "https://your-instance.cloud.akeneo.com"
CLIENT_ID = "your_client_id"
SECRET = "your_secret"
USERNAME = "your_api_user"
PASSWORD = "your_api_password"
def get_token():
r = requests.post(
f"{AKENEO_URL}/api/oauth/v1/token",
json={
"grant_type": "password",
"client_id": CLIENT_ID,
"client_secret": SECRET,
"username": USERNAME,
"password": PASSWORD,
}
)
r.raise_for_status()
return r.json()["access_token"]Step 2 — Fetch products with pagination
def fetch_all_products(token):
headers = {"Authorization": f"Bearer {token}"}
products = []
url = f"{AKENEO_URL}/api/rest/v1/products?limit=100&with_attribute_options=true"
while url:
r = requests.get(url, headers=headers)
r.raise_for_status()
data = r.json()
products.extend(data.get("_embedded", {}).get("items", []))
# Pagination
next_link = data.get("_links", {}).get("next", {}).get("href")
url = next_link if next_link else None
# NOTE: This does NOT fetch product models — variants will be
# missing parent attributes. You need additional calls to
# /product-models and hierarchy traversal. ~100 more lines.
return products- • Product model fetching and hierarchy traversal (another 80+ lines)
- • Attribute inheritance resolution (merge parent + child attributes)
- • Token refresh handling (tokens expire after 1 hour)
- • Rate limit backoff (Akeneo limits requests per minute)
- • Error recovery and retry logic
- • Deleted product detection
- • Incremental sync state management
Step 3 — Create schema and upsert
def setup_db(conn):
with conn.cursor() as cur:
cur.execute("""
CREATE TABLE IF NOT EXISTS products (
id TEXT PRIMARY KEY,
sku TEXT NOT NULL,
family TEXT,
enabled BOOLEAN,
categories TEXT[],
created_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ,
data JSONB
);
CREATE INDEX IF NOT EXISTS idx_products_sku
ON products (sku);
CREATE INDEX IF NOT EXISTS idx_products_data
ON products USING GIN (data);
""")
conn.commit()
def upsert_product(conn, product):
with conn.cursor() as cur:
cur.execute("""
INSERT INTO products (id, sku, family, enabled, categories,
created_at, updated_at, data)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (id) DO UPDATE SET
sku = EXCLUDED.sku,
family = EXCLUDED.family,
enabled = EXCLUDED.enabled,
categories = EXCLUDED.categories,
updated_at = EXCLUDED.updated_at,
data = EXCLUDED.data;
""", (
product["identifier"],
product["identifier"],
product.get("family"),
product.get("enabled"),
product.get("categories", []),
product.get("created"),
product.get("updated"),
json.dumps(product.get("values", {})),
))
conn.commit()Option B: SyncPIM no-code setup (5 minutes)
SyncPIM handles everything in the Python script above — plus all the missing pieces (product models, rate limiting, incremental sync, error recovery) — without writing a line of code.
- 1
Sign up at syncpim.com
No credit card. You get 10 free exports immediately.
- 2
New configuration → Enter Akeneo credentials
URL, Client ID, Secret, Username, Password. Test button confirms connection.
- 3
Select PostgreSQL destination
Paste your connection string. Select the schema/table prefix. SyncPIM creates the table automatically.
- 4
Choose channel, locale, and optional enrichers
Select which Akeneo channel to export. Add URL slug computation or other enrichment rules if needed.
- 5
Click Run
Live progress counter. First export of 10k products: typically under 2 minutes. All product models flattened automatically.
The PostgreSQL schema SyncPIM creates
SyncPIM creates the same schema you'd write by hand, but with all indexes in place from the start:
-- Run these queries after your first SyncPIM export:
-- Count total products
SELECT COUNT(*) FROM products;
-- Get product by SKU
SELECT sku, family, enabled, data
FROM products
WHERE sku = 'YOUR-SKU';
-- Search by attribute value
SELECT sku, data->'color'->0->>'data' AS color
FROM products
WHERE family = 'clothing'
AND data @> '{"enabled": true}'
LIMIT 20;
-- Full-text search on product names
SELECT sku, data->'name'->'en_US'->0->>'data' AS name
FROM products
WHERE data->'name'->'en_US'->0->>'data' ILIKE '%blue%';Running incremental exports and verifying data
After your initial full export, enable incremental sync in SyncPIM. For the manual script, store the last export timestamp in a metadata table:
-- Manual script: track last export time
CREATE TABLE sync_state (
key TEXT PRIMARY KEY,
value TEXT
);
INSERT INTO sync_state VALUES ('last_export', NOW()::TEXT);
-- On next run, use:
SELECT value FROM sync_state WHERE key = 'last_export';
-- Then pass to Akeneo API as:
-- ?search={"updated":[{"operator":">","value":"<timestamp>"}]}SyncPIM handles this automatically — no state table needed. It tracks the last successful export per configuration and uses it on the next scheduled or manual run.
Skip writing the boilerplate
The Python script above is a starting point — not production code. SyncPIM handles OAuth2, pagination, product models, rate limits, retries, and incremental sync for you.