I have an old postgresql dump that is 1.4GB large containing hundreds of tables and its data.

in the file, there are first all table create statements and then all data inserts.

I can split the file into two using:


# devider:
STR='-- Data for Name:' 

# number of lines befotre the devider that should get into the bottom file already:

# get first occurrence of devider:
K=$(grep -n -- $STR $file_name |head -1|cut -d":" -f1)

# line count (N): 
N=$(wc -l < $file_name)

# length of the bottom file:
L=$(( $N - $K + $D))

# create the top of file: 
head -n $K $file_name > top_$file_name

# create bottom of file: 
tail -n $L $file_name > bottom_$file_name

(source: https://stackoverflow.com/a/3066968/1069083)

the top file now looks like this:

-- Name: access_tokens; Type: TABLE; Schema: public; Owner: someone

CREATE TABLE access_tokens (

ALTER TABLE access_tokens OWNER TO "someone";

-- Name: application_services; Type: TABLE; Schema: public; Owner: someone

Bottom file looks like this:

-- Data for Name: access_tokens; Type: TABLE DATA; Schema: public; Owner: someone

COPY access_tokens (id, user_id, device_id, token, last_used) FROM stdin;
4       @dagobertderkaufhauserpresser:matrix.test.com  \N      MDAxZWxvY2F0aW9uIG1hdHJpeC5lY2xhYnMuZGUKMDAxM2lkZW50aWZpZXIga2V5CjAwMTBjaWQgZ2VuID0gMQowMDQxY2lkIHVzZXJfaWQgPSBAZGFnb2JlcnRkZXJrYXVmaGF1c2VycHJlc3NlcjptYXRyaXguZWNsYWJzLmRlCjAwMTZjaWQgdHlwZSA9IGFjY2VzcwowMDFkY2lkIHRpbWUgPCAxNDU2ODY5OTY0MDk0CjAwMmZzaWduYXR1cmUgqG8q57PffosB-Aob1Szapvu-Bd7j9Sve9-6nPz7hL8QK      \N
5       @wobwob:matrix.test.com        \N      MDAxZWxvY2F0aW9uIG1hdHJpeC5lY2xhYnMuZGUKMDAxM2lkZW50aWZpZXIga2V5CjAwMTBjaWQgZ2VuID0gMQowMDJiY2lkIHVzZXJfaWQgPSBAd29id29iOm1hdHJpeC5lY2xhYnMuZGUKMDAxNmNpZCB0eXBlID0gYWNjZXNzCjAwMWRjaWQgdGltZSA8IDE0NTY4NzM3MTI3NzMKMDAyZnNpZ25hdHVyZSB-PHutqfj3BAB6Mtop5qdiAz70YGSL2Wz7v1w4gj83Zgo  \N

So how can I split the bottom file now into separate files with the name of the table as filename each?

  • Yes, so where's the name of the table? How can we know where to split? Where is the second table we should split at? Is the table name what is found after Name:? Is that the only time that Name: will appear in an entry?
    – terdon
    Mar 15, 2017 at 9:54
  • Yes, Name is only there. The blocks are all the same, starting like in the excerpts.
    – rubo77
    Mar 15, 2017 at 10:42

1 Answer 1

csplit -s -f file. bottom_$file_name '/^-- Data for Name:/-1' '{*}'
grep -oP '^-- Data for Name:\s*\K[^;]+' file.* | tr ':' ' ' | xargs -l -t mv

In a fresh directory, place your bottom sql file that you generated. Then csplit shall split this into multiple files & puts them in file.nnn format names. After that we grep out the table names from these split files & pass them on to xargs to be passed on to mv.

  • great, this works fine, I additionally changed the file endings with for i in *; do mv $i $i.sql; done
    – rubo77
    Mar 15, 2017 at 14:21
  • Or we could just replace the tr step with this: sed -e 's/:\(.*\)/ \1.sql/' This has the added advantage of not deleting any colons if present in table name & of course saving an additional mv.
    – user218374
    Mar 15, 2017 at 14:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .