# # SQL statements to create backup database for content-based backup # http://256stuff.com/gray/docs/content_based_backup/ # CREATE USER backup CREATEDB NOCREATEUSER; CREATE DATABASE backup WITH OWNER = backup ENCODING = 'SQL_ASCII'; SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false; SET SESSION AUTHORIZATION 'backup'; REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC; SET SESSION AUTHORIZATION 'backup'; SET search_path = public, pg_catalog; CREATE SEQUENCE backup_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; CREATE TABLE backups ( created timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone, id bigint NOT NULL, machine text NOT NULL, collection text NOT NULL, "comment" text, "full" boolean, new_files bigint, directories bigint, symlinks bigint, devices bigint, zero_files bigint, dup_files bigint, dup_size bigint, new_size bigint, gzip_size bigint, duration interval ); CREATE INDEX backups_id ON backups USING btree (id); CREATE TABLE files ( "type" integer NOT NULL, "path" text NOT NULL, linkpath text, size bigint NOT NULL, inode bigint, "mode" integer, nlink integer, mtime timestamp without time zone, ctime timestamp without time zone, content text, backup bigint NOT NULL, major integer, minor integer, "owner" text, "group" text ); CREATE INDEX files_path ON files USING btree ("path"); CREATE INDEX files_content ON files USING btree (content);