Postgresql pg_dumpall restore one database

postgresqlpostgresql-9.2

My environment:

# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.3 (Maipo)
# rpm -q postgresql
postgresql-9.2.18-1.el7.x86_64
#

I used following PostgreSQL: Documentation: 9.6: pg_dumpall to take dump all databases

$ pg_dumpall > db.out

from what I understand, following will restore all databases:

$ psql -f db.out postgres

my goal is, I need to restore just a one (single) database from that entire dump, how does one accomplish this task?

Best Answer

Your dump is in SQL format. Just open your db.out file remove what's for other databases.

Related Solutions

PostgreSQL read-only user for pg_dumpall

Backups from a normal user are a nightmare. If you're using PostgreSQL on Linux, I'd recommend relying on sudo (and the /etc/sudoers file) to accomplish this, so that a given specific user can sudo -u postgres pg_dumpall <params> with specific parameters, so that's all they can do as user postgres (and therefore have no write access to the database).

Improving Performance of RDS Instance Running PostgreSQL 9.6

The solution appears to be to run VACUUM FULL ANALYZE;. While I did see significant performance improvements after reindexing the databases, it was probably an unnecessary step. I should have just run VACUUM FULL ANALYZE;

This is the new Explain from instance 2 after running VACUUM FULL ANALYZE:

Aggregate  (cost=19242.48..19242.49 rows=1 width=8)
  ->  Nested Loop  (cost=4183.20..19242.42 rows=1 width=4878)
        ->  Hash Join  (cost=4182.77..19241.42 rows=1 width=4881)
              Hash Cond: (((((b.state)::text || (b.county)::text) || (b.tract)::text) || (b.blkgrp)::text) = (blkgrps_2015.geoid)::text)
              ->  Bitmap Heap Scan on g_2015_5 b  (cost=4174.21..17562.74 rows=222682 width=27)
                    Recheck Cond: ((sumlevel)::text = '150'::text)
                    ->  Bitmap Index Scan on sumlev_2015_idx  (cost=0.00..4118.54 rows=222682 width=0)
                          Index Cond: ((sumlevel)::text = '150'::text)
              ->  Hash  (cost=8.55..8.55 rows=1 width=4883)
                    ->  Index Scan using blkgrps_2015_gist on blkgrps_2015  (cost=0.28..8.55 rows=1 width=4883)
                          Index Cond: (geom && '0103000020E61000000100000031000000386F3E38F5EA55C04BB9C64D80F444408467CE6AF6EA55C06AB0B14A61F44440239A5B50FAEA55C0EAD24EC742F44440FC31CDD700EB55C083C6434925F444401F8783E409EB55C0881CBC5109F444403EEED54E15EB55C01CC1335BEFF34440B9C5C0E422EB55C0F5C65ED7D7F34440FEBFC06A32EB55C068B7372DC3F34440EAA6D79C43EB55C00AE43CB7B1F344409120B62F56EB55C08F69E4C1A3F34440185E05D269EB55C056A34D8A99F34440DA13CB2D7EEB55C0B2C2353D93F3444038A9E1E992EB55C0A01734F690F344406D377EABA7EB55C0D55F41BF92F34440ADB3BD17BCEB55C0E9298C9098F344401F8232D5CFEB55C08A0A9B50A2F3444069AD6B8DE2EB55C0890EBCD4AFF34440F7196FEEF3EB55C0F483BFE1C0F34440DF4320AC03EC55C01DECFA2CD5F34440DA698D8111EC55C09DAD8F5DECF34440D6751D321DEC55C06EF5EF0D06F444401B7A998A26EC55C0DD269BCD21F4444030370D622DEC55C0F2450A233FF44440B1CF7A9A31EC55C061F6C38D5DF444407A915E2133EC55C002FA8E887CF44440AC8B00F031EC55C07D93B98B9BF44440AC83920B2EEC55C0BAD46B0FBAF44440B0BC198527EC55C0FAAEFA8DD7F44440D0E924791EEC55C078953186F3F4444068864F0F13EC55C0E6AA887D0DF5444017AF947905EC55C0FDC53D0225F54440056C74F3F5EB55C04C1747AD39F544408F26EFC0E4EB55C0EEE116244BF544401FBE5B2DD2EB55C0D185281A59F5444022561D8ABEEB55C03D09505263F54440B5863E2DAAEB55C0C75DC69F69F544408810F86F95EB55C09AC3EDE66BF54440D58F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A5495D8158EB55C07D0E9E8B5AF54440189C62C845EB55C0FACDCD064DF54440D32BE26634EB55C0919EFBF83BF54440C20101A924EB55C07415E0AC27F544409524B4D316EB55C03712697B10F54440267792230BEB55C01FD333CAF6F44440A861CBCB01EB55C04B78CF09DBF4444009CF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440386F3E38F5EA55C04BB9C64D80F44440'::geometry)
                          Filter: _st_intersects(geom, '0103000020E61000000100000031000000386F3E38F5EA55C04BB9C64D80F444408467CE6AF6EA55C06AB0B14A61F44440239A5B50FAEA55C0EAD24EC742F44440FC31CDD700EB55C083C6434925F444401F8783E409EB55C0881CBC5109F444403EEED54E15EB55C01CC1335BEFF34440B9C5C0E422EB55C0F5C65ED7D7F34440FEBFC06A32EB55C068B7372DC3F34440EAA6D79C43EB55C00AE43CB7B1F344409120B62F56EB55C08F69E4C1A3F34440185E05D269EB55C056A34D8A99F34440DA13CB2D7EEB55C0B2C2353D93F3444038A9E1E992EB55C0A01734F690F344406D377EABA7EB55C0D55F41BF92F34440ADB3BD17BCEB55C0E9298C9098F344401F8232D5CFEB55C08A0A9B50A2F3444069AD6B8DE2EB55C0890EBCD4AFF34440F7196FEEF3EB55C0F483BFE1C0F34440DF4320AC03EC55C01DECFA2CD5F34440DA698D8111EC55C09DAD8F5DECF34440D6751D321DEC55C06EF5EF0D06F444401B7A998A26EC55C0DD269BCD21F4444030370D622DEC55C0F2450A233FF44440B1CF7A9A31EC55C061F6C38D5DF444407A915E2133EC55C002FA8E887CF44440AC8B00F031EC55C07D93B98B9BF44440AC83920B2EEC55C0BAD46B0FBAF44440B0BC198527EC55C0FAAEFA8DD7F44440D0E924791EEC55C078953186F3F4444068864F0F13EC55C0E6AA887D0DF5444017AF947905EC55C0FDC53D0225F54440056C74F3F5EB55C04C1747AD39F544408F26EFC0E4EB55C0EEE116244BF544401FBE5B2DD2EB55C0D185281A59F5444022561D8ABEEB55C03D09505263F54440B5863E2DAAEB55C0C75DC69F69F544408810F86F95EB55C09AC3EDE66BF54440D58F29AD80EB55C0CBEACA1D6AF54440F4E8CA3F6CEB55C0F9BC304C64F54440A5495D8158EB55C07D0E9E8B5AF54440189C62C845EB55C0FACDCD064DF54440D32BE26634EB55C0919EFBF83BF54440C20101A924EB55C07415E0AC27F544409524B4D316EB55C03712697B10F54440267792230BEB55C01FD333CAF6F44440A861CBCB01EB55C04B78CF09DBF4444009CF46F5FAEA55C0019CCFB3BDF444403450F2BDF6EA55C0AD77B7489FF44440386F3E38F5EA55C04BB9C64D80F44440'::geometry)
        ->  Index Scan using e_2015_5_0003000_pkey on e_2015_5_0003000  (cost=0.42..0.99 rows=1 width=19)
              Index Cond: (((stusab)::text = (b.stusab)::text) AND ((logrecno)::text = (b.logrecno)::text))

So, lesson learned, when launching an RDS instance from a Snapshot, run VACUUM FULL ANALYZE; before testing.

Best Answer

Related Solutions

PostgreSQL read-only user for pg_dumpall

Improving Performance of RDS Instance Running PostgreSQL 9.6

Related Question