Search Postgresql Archives

Re: getting list of tables from command line

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Craig White wrote:
I wrote a little script to individually back up table schemas, table
data and then vacuum the table and it works nicely but I wanted a way to
query a database and get a text file with just the table names and
cannot figure out a way to do that.

my script looks like this...
(all I want is to get a list of the tables into a text file pg_tables)

#/bin/sh
#
DB_NAME=whatever
#
for i in `cat pg_tables`
do
  pg_dump  --username=postgres \
--schema=db --table=$i \
    --schema-only \
    $DB_NAME > schemas/$i.sql
  pg_dump  --username=postgres \
    --schema=db \
    --table=$i \
    --data-only \
    $DB_NAME > data/$i.sql
  vacuumdb --username=postgres \
   --dbname=$DB_NAME \
   --table=db.$i \
   --verbose \
   --full
done

Is there a way to do that?

From the command line you can run:
psql mydbname -c "\d"
to get a list of all public table names.

Or just select the table names from pg_tables and send it to a file:
psql myDBname -c "SELECT tablename FROM pg_tables WHERE schemaname = 'someschema';" > my_tables.txt

This works on my 8.1 database on RHEL. You could also use something similar inside of your script to generate the table names and send them to pg_dump. As far as I know, if you specify a table name, but don't specify a schema name to pg_dump, it will automatically dump all tables with that name, irregardless of what schema they belong to. I'm sure someone will let me know if that's not correct.......

hth

Ron
Craig

PS there's a lack of cohesion between various commands such as vacuumdb
and pg_dump for things like '--schema'


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux