2 edited tags
| link
1
source | link

How to manipulate bytea data when using backup script as a template

I have a project with a web app that has many configurable options, with the configurations stored in a PostgreSQL database. Most fields are text or ints, but there are a couple configurations stored as JSON decoded into bytea.

We have one "default configuration" setup script that my team (Software Quality Assurance) utilizes to put everyone's testing server on the same page. The script takes a few hours to manually create data and store configurations via the web app. I'd like to save our team hours of setup time by simply sharing a SQL file with the create / insert statements necessary to get the database to the same configuration in way less time.

So with a script such as:

CREATE DATABASE example;

...

CREATE TABLE example_table (
    id integer NOT NULL,
    ip_address text,
    domain text,
    configuration bytea
);

...

COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1   0.0.0.0 invalid.domain.com  \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c277265666572656e63655f746f5f646f6d61696e273a27696e76616c69642e646f6d61696e2e636f6d277d
\.

--- The above bytearray is "{'reference_to_ip_address':'0.0.0.0','reference_to_domain':'invalid.domain.com'}"

Originally, my thought was to replace "0.0.0.0" and "invalid.domain.com" with whatever the user wanted, and then the user would execute the script to setup their database with the common configuration. However, with the encoded data, the end result would have invalid references to the server's IP address and domain. Has anyone ever had a problem like this and figured out a solution? Is there a better way of doing this? I'm open to any suggestions other than changing the database organization (I can't swing that one).