9. Tools to Move Data in and out of PostgreSQL | PostgreSQL for Data Architects

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents
Feedback & Rating

PostgreSQL for Data Architects

By : Jayadevan M

5 (2)

PostgreSQL for Data Architects

5 (2)

By: Jayadevan M

Overview of this book

This book is for developers and data architects who have some exposure to databases. It is assumed that you understand the basic concepts of tables and common database objects, including privileges and security.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Installing PostgreSQL

1. Installing PostgreSQL

Installation options

Dependencies to compile the source

Configuring and creating the makefile

Building and creating the executables

Installing and moving the files to where they belong

Initializing a cluster

Working with extensions

Summary

2. Server Architecture

2. Server Architecture

Starting with the daemon process

Understanding the shared buffer

Checkpoint

WAL and the WAL writer process

The background writer

The autovacuum launcher process

The logging process

The stats collector process

The WAL sender and WAL receiver

Sorting in memory with work_mem

Maintenance with maintenance_work_mem

Summary

3. PostgreSQL – Object Hierarchy and Roles

3. PostgreSQL – Object Hierarchy and Roles

The PostgreSQL cluster

Understanding tablespaces

Databases, schemas, and search_path

Roles and privileges

Summary

4. Working with Transactions

4. Working with Transactions

Understanding transactions

PostgreSQL and MVCC

Summary

5. Data Modeling with SQL Power Architect

5. Data Modeling with SQL Power Architect

Tools for databases and their uses

Database design tools

Generating SQL

Summary

6. Client Tools

6. Client Tools

GUI tools and command-line tools

pgAdmin – downloading and installation

psql – working from the command line

Summary

7. SQL Tuning

7. SQL Tuning

Understanding basic facts about databases

Query execution components

Finding the execution plan

Optimization guidelines and catches

Summary

8. Server Tuning

8. Server Tuning

Server-wide memory settings

Managing writes, connections, and maintenance

Seek/scan cost and statistics parameters

Materialized views

Partitioned tables

Summary

9. Tools to Move Data in and out of PostgreSQL

9. Tools to Move Data in and out of PostgreSQL

Setting up the production database – considerations

COPY

Fast loading with pg_bulkload

pg_dump

Filtering options

Summary

10. Scaling, Replication, and Backup and Recovery

10. Scaling, Replication, and Backup and Recovery

Scalability

Point-in-time recovery

Summary

11. PostgreSQL – Troubleshooting

11. PostgreSQL – Troubleshooting

Connection issues

Authentication and permission issues

Parameter changes not effective

Query not responding

Summary

12. PostgreSQL – Extras

12. PostgreSQL – Extras

Interesting data types

Features over time

Summary

Index

Index

Customer Reviews

5 (2)

5 star

100%

4 star

0%

3 star

0%

2 star

0%

1 star

0%

COPY

Now, let's look at the easiest way of moving data from PostgreSQL tables to files or the other way around: the COPY command. We will see the table to file options first:

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# CREATE TABLE myt (id int, nm varchar(20));
CREATE TABLE
test=# INSERT INTO myt VALUES(1,'First record');
INSERT 0 1
test=# INSERT INTO myt VALUES(2,'Second record');
INSERT 0 1
test=# COPY myt TO '/tmp/file.csv';
COPY 2
test=# \! cat /tmp/file.csv
1 First record
2 Second record

The simplest use of the command is COPY TABLE TO FILE. Instead of using the table name, we can use a SELECT command:

test=# \! rm /tmp/file.csv
test=# COPY ( select * from myt ) to '/tmp/file.csv';
COPY 2
test=# \! head /tmp/file.csv
1  First record
2  Second record

We named the file with the .csv extension, but the file generated is not really separated by comma. It used text as the default format with tab as the column separator. For the .csv output, we have...

Visually different images

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PostgreSQL for Data Architects

Search

Your notes and bookmarks