Database Reference
In-Depth Information
When trying to delete from
user_info
, you will notice that you can't do it without
dropping a foreign key from
messages.from_user
.
Here, we could decide that it is OK to keep the messages on the receivers partition
only, and if needed, that the sent messages can be retrieved using a
RUN ON ALL
function. So, we will drop the foreign key from
messages.from_user
.
psql p0 -c 'alter table message drop constraint
message_from_user_fkey
There are other options when splitting the data that requires less disk space usage
for database system if you are willing to do more manual work.
For example, you can copy over just the schema using
pg_dump -s
and then use
COPY
from an SQL statement to move over just the needed rows:
pg_dump -s chap9 | psql p0
psql chap9 -c "COPY (select * from messages
where hashtext(to_user) & 3 = 0) TO stdout" |
psql p0 -c 'COPY messages FROM stdin'
...
Or even set up a specially designed Londiste replica and do the switch from single
database to partitioned cluster in only seconds once the replica has reached a stable
state.
