PG_Restore longrunning

Question

PG_Restore longrunning

PG_Restore longrunning morethan 24 hrs on azure postgresql server ( Standard_D4ds_v4 (4 vCores, 16 GiB memory, 6400 max iops) source database size is 2.8 TB .How to improve the pg_restore performance duration pg_dump only taking 8 hrs

1 answer

Your answer

Answer 1

Q&A Assist

AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

To improve the performance duration of pg_restore on your Azure PostgreSQL server, consider the following best practices:

Use Parallel Restore: Utilize multiple concurrent jobs during the restore process. The number of jobs can be equal to or less than the number of vCPUs allocated for the target server. For example, if you have 4 vCores, you can set the -j option to 4 or fewer.
Optimize Server Parameters: Before running pg_restore, you can optimize certain server parameters:
- Set work_mem to a higher value (e.g., 32 MB).
- Increase max_wal_size to 64 GB.
- Set checkpoint_timeout to 60 minutes.
- Adjust maintenance_work_mem to 2 GB.
- Disable autovacuum during the restore process.
- Enable wal_compression. After the restore, ensure to revert these parameters to their appropriate values based on your workload requirements.
Disable High Availability (HA): If HA is enabled, consider disabling it before running pg_restore to reduce overhead.
Analyze Tables Post-Restore: After the restore is complete, analyze all tables to ensure that statistics are updated, which can improve query performance.
Review Logs for Errors: Always check the errors.log file after the restore process to address any issues that may have occurred during the operation.

By implementing these strategies, you should be able to reduce the duration of your pg_restore process significantly.

References:

Sivangnanam, Kuraliniyan (AWM, IRL) 20 Reputation points

2025-11-26T16:12:21.0766667+00:00

Any other suggestions?
Kalyani Kondavaradala 4,595 Reputation points Microsoft External Staff Moderator

2025-11-27T12:43:00.06+00:00
Hi Sivangnanam, Kuraliniyan (AWM, IRL),

Check for Locks or Conflicts: Occasionally, locks on tables or rows can inhibit the restore speed. Make sure there aren’t any long-running transactions that might be blocking your restore.

Backup Size and Format: Since your pg_dump took 8 hours, double-check the output format of your dump and whether any compression was used. Using a directory format (-Fd) with compression can make restores faster. If your dump is very large, consider breaking it into smaller chunks.

and also, as suggested in the above answer you can use job number

pg_restore --jobs=4 -d your_database_name your_dump_file

What version of PostgreSQL are you using on your Azure Database for PostgreSQL?

Reference document https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-pgdump-restore#syntax-for-pg_restore
Sivangnanam, Kuraliniyan (AWM, IRL) 20 Reputation points

2025-11-27T13:56:08.2266667+00:00
Dropping and creating new database then starting pg_restore

format custom using for pg_dump

dump size is 150 GB(source database size in2 .8 TB)4. How to breaking it into smaller chunks.?

Version16.10

Kalyani Kondavaradala 4,595 Microsoft External Staff Moderator

Hi Sivangnanam, Kuraliniyan (AWM, IRL),

Re-Dump with Directory Format:

Use -F d (Directory format) with parallel jobs (-j 4) to create the archive.This creates the necessary internal structure (separate files for tables/indexes) that allows the rest of the steps to use true parallelism.

pg_dump -F d -j 4 -Z 0 -f /path/to/dump_directory <database_name>

-Z 0: Disables compression (optional, but often faster). If the network/disk IO is your bottleneck, keep compression (-Z 5 or zstd) or pipe to a separate compression utility.

Then try to run pg_restore , this is the reason you are seeing slowness

`pg_dump`	Sequential Read (Data extraction)	Mostly Disk Read I/O. Minimal CPU/Server load.
`pg_restore`	Data Write, Index Build, Constraint Check	High Disk Write I/O, High CPU (for indexing), High Memory.

Kalyani Kondavaradala 4,595 Reputation points Microsoft External Staff Moderator

2025-12-01T09:54:15.63+00:00

Hi Sivangnanam, Kuraliniyan (AWM, IRL),

Just checking to see if you have a chance to check my previous response and helped, do let me know if you have any further questions on this.
Sivangnanam, Kuraliniyan (AWM, IRL) 20 Reputation points

2025-12-03T17:00:23.9666667+00:00

Thank you .In dir format.Is it possible to keep like single file name ?
Kalyani Kondavaradala 4,595 Reputation points Microsoft External Staff Moderator

2025-12-04T10:26:58.2033333+00:00

Hi Sivangnanam, Kuraliniyan (AWM, IRL)

You cannot specify a single file name when using the Directory format (-F d) because its entire purpose is to create a directory containing multiple individual files(Large data file into smaller chunks)

I hope this has been helpful!

If you still have questions, please let us know what is needed in the comments so the question can be answered.

If this answers your query, please click Accept Answer and Upvote for was this answer helpful.

Thanks!

Kalyani

Share via

PG_Restore longrunning

1 answer

Your answer