0

I am new to database administration. I've been scraping a website for a month now. I have two drives, each 2TB in size. I have PostgreSQL 10 on my first drive (c:) and it has 50GB left. I've only scraped about 75% of the website and have another 25% to go. What are my options?

I can't extend my first drive because my computer is old (hp z600) and the RAID software only supports up to 2TB. I would prefer not to stop my scraping process because I was an idiot and didn't keep track of pages that I've already visited. I thought 2TB would be enough, but I was wrong.

Running SHOW autovacuum; shows that autovacumm is on.

|improve this question
2

It depends on how your database is architectured (you do not say) and may also depend on the PostgreSQL version you are using (you do not say).

Anyway, in general, with PostgreSQL, you can create a new "tablespace" that is to make a specific new directory (so you choose the place, like on second drive) available to create new PostgreSQL objects, such as tables, in it.

See https://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html that says Tables, indexes, and entire databases can be assigned to particular tablespaces. and gives this example:

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
CREATE TABLE foo(i int) TABLESPACE space1;

Also, are you sure that the current content is really only data and not temporary stuff or useless indexes or just overblown tables because you are not running autovacuum or things like that (this depends a lot on which PostgreSQL version you run)?

If you have only a single table, with really 1950 GB of data in it, you will first need to partition it, in order to be able to move some of the content to a new tablespace (and/or archive some old content somewhere else).

|improve this answer
  • I am using PostgreSQL 10. I installed it with pretty much default settings. But will I be able to move an existing table to the second drive? I am not sure if all the data is actual data. I need to research autovacuum – Learning Jun 5 '18 at 23:42
  • running SHOW autovacuum; shows that autovacumm is on. – Learning Jun 5 '18 at 23:58
2

Your chances of fixing this without interrupting the scraping process seem pretty poor. If you didn't keep track track of which pages you already visited, how will it know when it is done in the first place? Is it tracked only in the RAM of the scraping process? And you can't reverse engineer that data out from whatever it is you did record into the database?

Does the scraper only insert, or does it have to refer back to already inserted data while it operates? If it only inserts, your best bet would be open a new session and take an Access Exclusive lock on your main (largest) table. Once that is acquired (assuming it can be--it is possible that your scraping process has an Access Share lock or stronger and will never give that lock up, in which case you are probably screwed) than your scraper will block the next time it needs to use that table. Then copy all the data out of that table (COPY command, or \copy command) into your spare drive, truncate the table, and then release the lock to let your process resume.

Then you can worry about stitching your data back together again once your scraper finishes.

|improve this answer
  • Thanks for the input. I end up stopping the scraper and started to create partitions. I had to implement a checker into the scraper to remove pages that was already scraped. – Learning Jun 22 '18 at 23:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.